update 多表关联。

[afeisky]

两个表结构是一样的，customer2是有最新的数据。
而customer1的数据要从customer2得到。根据id1,id2来判断。
customer1(id1,id2,name1,name2)
customer2(id1,id2,name1,name2)

现在我想根据customer2的数据更新到customer1中。

在SQL 2000中是这样写的，
update customer1
set name1=b.name1
name2=b.name2
select customer1 a,
customer2 b
where a.id1=b.id1
and a.id2=b.id2

这该怎么改? 谢谢

[深圳李春雷]

update customer1 x
set (name1,name2)=(select name1,name2 from customer2 y where x.id1=y.id1 and x.id2=y.id2)

[lifei88]

up

[jasoncheung]

update customer1 a
set (a.name1,a.name2)=(select b.name1 ,b.name2 from customer2 b
where a.id1=b.id1
and a.id2=b.id2 )

[yangqingdelphi]

楼上的方法正确!

[beckhambobo]

update customer1 a
set (name1,name2)=(select name1,name2 from customer2 b where a.id1=b.id1
and a.id2=b.id2)

[beckhambobo]

update customer1 a
set (name1,name2)=(select name1,name2 from customer2 b where a.id1=b.id1
and a.id2=b.id2) where exists(select 1 from customer2 b where a.id1=b.id1
and a.id2=b.id2)

[tjwk]

up

[xinpingf]

我觉得where里面两表的顺序应该倒过来更好些

update customer1 a
set (name1,name2)=(select name1,name2 from customer2 b where id1=a.id1
and id2=a.id2)