多表关联同时更新多条不同的记录方法分享_mssql数据库教程-查字典教程网
多表关联同时更新多条不同的记录方法分享
多表关联同时更新多条不同的记录方法分享
发布时间:2016-12-29 来源:查字典编辑
摘要:以下为测试例子。1.首先创建两张临时表并录入测试数据:复制代码代码如下:createtable#temptest1(idint,name1v...

以下为测试例子。

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

Ont1.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

相关阅读
推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
  • 大家都在看
  • 小编推荐
  • 猜你喜欢
  • 最新mssql数据库学习
    热门mssql数据库学习
    编程开发子分类