如果我想将一个表中的某些字段的数据更新到另一个表,以关健字pd_ID为相关联,又怎样写SQL呢?例如: A表: pd_ID name expression 1 test1 abc
2 test2 asdf
3 test3 zxcv
B表: pd_ID name expression 1 test1 qwer
2 test2 lkjh
3 test3 piuu
4 test4 uopp我想将A表中的expression字段与B表中相关联的更新,只是更新A表中有记录的数据。
2 test2 asdf
3 test3 zxcv
B表: pd_ID name expression 1 test1 qwer
2 test2 lkjh
3 test3 piuu
4 test4 uopp我想将A表中的expression字段与B表中相关联的更新,只是更新A表中有记录的数据。
是不是:update A set A.expression=B.expression,A.expression1=B.expression1,A.expression2=B.expression2 from B where A.pd_ID=B.pd_ID
这可以了呢?或者是waterfirer(水清)的写法:
update b set expression=(select expression from a where a.pd_ID=b.pd_ID),expression1=(select expression1 from a where a.pd_ID=b.pd_ID) where exists(select 1 from a where a.pd_ID=b.pd_ID)呢?
set A.expression=B.expression
from A,B
where A.pd_ID=B.pd_ID and A.name=B.name
insert A
select 1,'test1','abc' union all
select 2,'test2','asdf' union all
select 3,'test3','zxcv'
select * from Acreate table B(pd_ID int,name varchar(10),expression varchar(10))
insert B
select 1,'test1','qwer' union all
select 2,'test2','lkjh' union all
select 3,'test3','piuu' union all
select 4,'test4','uopp'
select * from Bbegin tran
update A set A.name=B.name,A.expression=B.expression
from A,B where A.pd_ID=B.pd_ID and A.name=B.name--update A set name=(select name from B where pd_ID=A.pd_ID),
--expression=(select expression from B where pd_ID=A.pd_ID) select * from A
rollback trandrop table A,B