设为首页收藏本站
查看: 107|回复: 0

[PHP] 多表关联同时更新多条不同的记录方法分享

[复制链接]

论坛元老

Rank: 6Rank: 6

积分
34274
主题
17031
UID
1347
M币
67
贡献
17176

  • 发表于 2018-7-14 20:48:00 | 显示全部楼层 |阅读模式
    因为项目要求实现一次性同时更新多条不同的记录的需求,和同事讨论了一个比较不错的方案,这里供大家参考下

    以下为测试例子。
    1.首先创建两张临时表并录入测试数据:
    代码如下:
    create table #temptest1
    (
    id int,
    name1 varchar(50),
    age int
    )
    create table #temptest2
    (
    id int,
    name1 varchar(50),
    age int
    )  查询出此时的表数据为:
    #temptest1 #temptest2
      
      
    2.现在要将#temptest2中的年龄更新到相应的#temptest1中的年龄。
    其实就是让[表1]中ID为1的年龄改成19,同时ID为2的年龄改成20。
    当然这里的要求是只用一句SQL,不能用循环
    结果如下:

      
    实现方法如下:
    Update t1
    Set t1 .age = t2.age
    From #temptest1 t1
    Join #temptest2 t2
    On t1.id = t2.id
      
    (补充)Sql Server 2008 Merge命令写法:
    merge into #temptest1 t1
    using(select age,id from #temptest2) t2
    on t1.id = t2.id
    when matched then
    update set t1.age = t2.age
      
    是不是挺有趣的Sql。
    如何一次性更新多条不同值的记录
    标题可能没说清楚,假设有这样两张表:
    代码如下:
    create table testA(
    id number,
    eng varchar2(3),
    chi varchar2(3)
    )
    create table testB(
    id number,
    eng varchar2(3),
    chi varchar2(3),
    anythingother varchar2(1)
    ) 现有记录
    testA:
    ID ENG CHI
    ===============
    1 a 一
    2 b 二
    3 c 三
    testB:
    ID ENG CHI ANY....
    =================
    1 d 四
    2 e 五
    3 f 六
    我想把testB中的记录的ENG,CHI字段更新到testA中去,以ID来对应。
    CODE:
    SQL> set autot on
    SQL> update ta set ta.b=(select tb.b from tb where ta.a=tb.a) where exists (select 1 from tb where ta.a=tb.a);
    已更新4行。
    已用时间: 00: 00: 00.01
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1137212925
    --------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 5 | 165 | 20 (30)| 00:00:01 |
    | 1 | UPDATE | TA | | | | |
    |* 2 | HASH JOIN SEMI | | 5 | 165 | 5 (20)| 00:00:01 |
    | 3 | TABLE ACCESS FULL | TA | 5 | 100 | 2 (0)| 00:00:01 |
    | 4 | VIEW | VW_SQ_1 | 4 | 52 | 2 (0)| 00:00:01 |
    | 5 | TABLE ACCESS FULL| TB | 4 | 52 | 2 (0)| 00:00:01 |
    |* 6 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - access("TA"."A"="ITEM_1")
    6 - filter("TB"."A"=:B1)
    Note
    -----
    - dynamic sampling used for this statement (level=2)
    统计信息
    ----------------------------------------------------------
    0 recursive calls
    4 db block gets
    23 consistent gets
    0 physical reads
    1004 redo size
    840 bytes sent via SQL*Net to client
    856 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    4 rows processed
    SQL> update ta set ta.b=(select tb.b from tb where ta.a=tb.a) where ta.a= (select tb.a from tb where ta.a=tb.a);
    已更新4行。
    已用时间: 00: 00: 00.00
    执行计划
    ----------------------------------------------------------
    Plan hash value: 3571861550
    ----------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 1 | 20 | 7 (15)| 00:00:01 |
    | 1 | UPDATE | TA | | | | |
    |* 2 | FILTER | | | | | |
    | 3 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |
    |* 4 | TABLE ACCESS FULL| TB | 1 | 13 | 2 (0)| 00:00:01 |
    |* 5 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - filter("TA"."A"= (SELECT "TB"."A" FROM "TB" "TB" WHERE
    "TB"."A"=:B1))
    4 - filter("TB"."A"=:B1)
    5 - filter("TB"."A"=:B1)
    Note
    -----
    - dynamic sampling used for this statement (level=2)
    统计信息
    ----------------------------------------------------------
    11 recursive calls
    1 db block gets
    53 consistent gets
    0 physical reads
    588 redo size
    840 bytes sent via SQL*Net to client
    858 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    4 rows processed

    如果 create unique index tb_a_uidx on tb(a);
    [Copy to clipboard] [ - ]
    CODE:
    SQL> update (select ta.b tab1 ,tb.b tbb from ta,tb where ta.a=tb.a) set tab1=tbb;
    已更新4行。
    已用时间: 00: 00: 00.01
    执行计划
    ----------------------------------------------------------
    Plan hash value: 1761655026
    ----------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------
    | 0 | UPDATE STATEMENT | | 4 | 184 | 5 (20)| 00:00:01 |
    | 1 | UPDATE | TA | | | | |
    |* 2 | HASH JOIN | | 4 | 184 | 5 (20)| 00:00:01 |
    | 3 | TABLE ACCESS FULL| TB | 4 | 104 | 2 (0)| 00:00:01 |
    | 4 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    2 - access("TA"."A"="TB"."A")
    Note
    -----
    - dynamic sampling used for this statement (level=2)
    统计信息
    ----------------------------------------------------------
    8 recursive calls
    4 db block gets
    17 consistent gets
    0 physical reads
    1004 redo size
    840 bytes sent via SQL*Net to client
    827 bytes received via SQL*Net from client
    3 SQL*Net roundtrips to/from client
    3 sorts (memory)
    0 sorts (disk)
    4 rows processed
    回复

    使用道具 举报

    您需要登录后才可以回帖 登录 | 立即注册

    本版积分规则

    在我站开通SVIP可同时获得17个站点VIP资源 立即登录 立即注册
    快速回复 返回顶部 返回列表