求一条SQL语句，同一个表批量更新

神马被哪个妞占用了呢 2019-04-25 01:39:37

表如上，可以一条语句更新：把C列为2012的B列更新为 A列在C列为2014的B列不？
更新结果为2012的（A）12345对应成（B）ABCDE

...全文

238 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

神马被哪个妞占用了呢 2019-04-25

打赏
举报

都正确，感谢大大

二月十六版主 2019-04-25

打赏
举报

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([A] int,[B] nvarchar(21),[C] NVARCHAR(20))

Insert #T

select 1,N'A','2014' union all

select 2,N'B','2014' union all

select 3,N'C','2014' union all

select 4,N'D','2014' union all

select 5,N'E','2014' union all

select 1,N'F','2013' union all

select 2,N'G','2013' union all

select 3,N'H','2013' union all

select 4,N'I','2013' union all

select 5,N'J','2013' union all

select 1,N'K','2012' union all

select 2,N'L','2012' union all

select 3,N'M','2012' union all

select 4,N'N','2012' union all

select 5,N'P','2012'

Go

--测试数据结束

--更新语句

UPDATE t1

SET T1.B = T2.B

FROM #T t1,

     #T t2

WHERE t1.C = '2012'

      AND t2.C = '2014'

      AND t1.A = t2.A;



--测试结果

SELECT * FROM #T

Dear SQL(燊) 2019-04-25

打赏
举报



select A='1',B='A',C='2014' INTO #LIST UNION ALL
select A='2',B='B',C='2014' UNION ALL
select A='3',B='C',C='2014' UNION ALL
select A='4',B='D',C='2014' UNION ALL
select A='5',B='E',C='2014' UNION ALL
select A='1',B='F',C='2013' UNION ALL
select A='2',B='G',C='2013' UNION ALL
select A='3',B='H',C='2013' UNION ALL
select A='4',B='I',C='2013' UNION ALL
select A='5',B='J',C='2013' UNION ALL
select A='1',B='K',C='2012' UNION ALL
select A='2',B='L',C='2012' UNION ALL
select A='3',B='M',C='2012' UNION ALL
select A='4',B='N',C='2012' UNION ALL
select A='5',B='P',C='2012'


update a set b=b.B
from #list a 
inner join (select * from #list where c='2014') b on a.a=b.A
where a.C='2012'

select * from #list

/*
A    B    C
---- ---- ----
1    A    2014
2    B    2014
3    C    2014
4    D    2014
5    E    2014
1    F    2013
2    G    2013
3    H    2013
4    I    2013
5    J    2013
1    A    2012
2    B    2012
3    C    2012
4    D    2012
5    E    2012

(15 行受影响)

*/