34,590
社区成员
发帖
与我相关
我的任务
分享
create index idx_tb on tb(Name)
create index idx_tb on tb(cID)--如果CID本来有索引就不需要
update tb set
Age =(select top 1 Age from tb t where cID='b' and t.Name=tb.Name)
where cID='a'
再试试
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(id int, cID varchar(10), Name varchar(10),Age int)
insert tb select 1 ,'A','A1', 12
insert tb select 2 ,'A','A2', 34
insert tb select 3 ,'A','A3', 33
insert tb select 4 ,'A','A4', 34
insert tb select 5 ,'B','A1', 22
insert tb select 6 ,'B','A2', 33
insert tb select 7 ,'B','A3', 44
insert tb select 8 ,'B','A4', 43
SELECT * INTO TB1 FROM tb
WHERE cid='A'
SELECT * INTO TB2 FROM tb
WHERE CID='B'
SELECT * FROM TB1
SELECT * FROM TB2
MERGE DBO.TB1 AS Target
using(SELECT * FROM tb2) AS source
ON target.name=source.name
WHEN matched THEN
UPDATE
SET target.age=source.age
-----WHEN NOT matched BY target THEN
OUTPUT $action, Inserted.*, Deleted.*;
SELECT * FROM tb1
update tb set
Age =(select top 1 Age from tb t where cID='b' and t.Name=tb.Name)
where cID='a'
这样试试create table #表A(id int, cID varchar(10), Name varchar(10),Age int)
insert #表A select 1 ,'A','A1', 12
insert #表A select 2 ,'A','A2', 34
insert #表A select 3 ,'A','A3', 33
insert #表A select 4 ,'A','A4', 34
insert #表A select 5 ,'B','A1', 22
insert #表A select 6 ,'B','A2', 33
insert #表A select 7 ,'B','A3', 44
insert #表A select 8 ,'B','A4', 43
update a
set a.age=b.age
from #表A a,#表A b
where a.cid='A' and b.cid='B'
and a.name=b.name
select * from #表A
id cID Name Age
----------- ---------- ---------- -----------
1 A A1 22
2 A A2 33
3 A A3 44
4 A A4 43
5 B A1 22
6 B A2 33
7 B A3 44
8 B A4 43
(所影响的行数为 8 行)
--> 生成测试数据表:tb
if OBJECT_ID('tb') is not null drop table tb
go
create table tb(id int, cID varchar(10), Name varchar(10),Age int)
insert tb select 1 ,'A','A1', 12
insert tb select 2 ,'A','A2', 34
insert tb select 3 ,'A','A3', 33
insert tb select 4 ,'A','A4', 34
insert tb select 5 ,'B','A1', 22
insert tb select 6 ,'B','A2', 33
insert tb select 7 ,'B','A3', 44
insert tb select 8 ,'B','A4', 43
update tb set
Age =(select Age from tb t where cID='b' and t.Name=tb.Name)
where cID='a'
select * from tb
/*
id cID Name Age
----------- ---------- ---------- -----------
1 A A1 22
2 A A2 33
3 A A3 44
4 A A4 43
5 B A1 22
6 B A2 33
7 B A3 44
8 B A4 43
(8 行受影响)
*/
create table #ta(id int, cID varchar(10), Name varchar(10),Age int)
insert #ta select 1 ,'A','A1', 12
insert #ta select 2 ,'A','A2', 34
insert #ta select 3 ,'A','A3', 33
insert #ta select 4 ,'A','A4', 34
insert #ta select 5 ,'B','A1', 22
insert #ta select 6 ,'B','A2', 33
insert #ta select 7 ,'B','A3', 44
insert #ta select 8 ,'B','A4', 43
update a
set a.Age=b.age
from #ta a ,(select * from #ta where cid='B') b
where a.name=b.name and a.cid='A'
select * from #ta
id cID Name Age
----------- ---------- ---------- -----------
1 A A1 22
2 A A2 33
3 A A3 44
4 A A4 43
5 B A1 22
6 B A2 33
7 B A3 44
8 B A4 43
(8 行受影响)
update k
set age=b.age
from a k join a b on k.name=b.name
where k.cid='A' and b.cid='B'