如何用update from
TABLE 1
ID C1 C2 C3
1 XX YY ZZ
2 XX1 YY1 ZZ1
3 XX2 YY2 ZZ2
TABLE 2 把C1C2C3 通过表1 放进去
ID C1 C2 C3
1
2
3
如何用 update from啊
我写的是
update table2 set c1=(select top 1 c1 from table1 where id =table2.id),
c2=(select top 1 c2 from table1 where id =table2.id)
c3=(select top 1 c3 from table1 where id =table2.id)
这种方法很死，而且没有效率
我想用
update table2
set c1=t.c1,c2=t.c2,c3=t.c3
from (select top 1 c1,c2,c3 from table1 where id=table1.id ) t
为什么不行。报错。。。
[最优解释]
UPDATE b
SET b.c1 = a.c1 ,
b.c2 = a.c2 ,
b.c3 = a.c3
FROM TABLE1 a
INNER JOIN TABLE2 b ON a.id = b.id
update table_2 set c1=a.c1,c2 = a.c2,b.c3 = a.c3
from table_1 a
where table_2.id=a.id
update table2 set C1=b.c1,c2=b.c2,c3=b.c3
from table2 as a inner join table1 as b on a.id=b.id
create table a
(
id int not null,
a varchar(50) null,
b varchar(50) null,
c varchar(50) null,
d varchar(50) null
)
create table b
(
id int not null,
a varchar(50) null,
b varchar(50) null,
c varchar(50) null,
d varchar(50) null
)
insert into a
select 1,'this','is','no','problem' union
select 2,'maybe','you','are','wrong' union
select 3,'please','try','align','thanks'
insert into b(id)
select 1 union
select 2 union
select 3
update b set a=a.a,b = a.b,c = a.c,d=a.d
from a
where b.id=a.id
select * from b
