例如:
a,b,c 是表t的3个字段,通过 条件1 和 条件2 可以分别定位到一条记录
select a,b,c from t where 条件1
select a,b,c from t where 条件2
现在想把条件2 对应的记录分别修改位条件1对应的记录
update t set a =(select a from t where 条件1),b=(select b from t where 条件1),c=(select c from t where 条件1) where 条件2
有木有简单点的写法.. 譬如
update t set (a,b,c)=(select a,b,c from t where 条件1) where 条件2
求SQL大婶
------解决方案--------------------
update a set
a = b.a
,b=b.b
,c=b.c
from t a,t b
where (a.条件1) and (b.条件2)
------解决方案--------------------
--> 测试数据: @A
declare @A table (id int,c1 varchar(1),c2 varchar(1),c3 varchar(1))
insert into @A
select 1,'a','b','c' union all
select 2,'d','e','f' union all
select 3,'g','h','i'
--> 测试数据: @B
declare @B table (id int,c1 varchar(1),c2 varchar(1),c3 varchar(1))
insert into @B
select 4,'j','k','l' union all
select 5,'m','n','o' union all
select 6,'p','q','r' union all
select 7,'s','t','u'
--例如更新@A的第二条变成@B的id=6的数据
update @A
set c1=b.c1 ,c2=b.c2,c3=b.c3
from @A a,@B b where a.id=2 and b.id=6
select * from @A
/*
id c1 c2 c3
----------- ---- ---- ----
1 a b c
2 p q r
3 g h i
*/