如果我想将一个表中的某些字段的数据更新到另一个表,以关健字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表中有记录的数据。
解决方案:
update product_all set product_all.pd_place=product.pd_place,product_all.pd_eplace=product.pd_eplace,product_all.pd_model=product.pd_emodel,product_all.pd_brand=product.pd_brand,product_all.pd_ebrand=product.pd_ebrand,product_all.pd_para=product.pd_para,product_all.pd_epara=product.pd_epara,product_all.pd_sell=product.pd_sell from product where product_all.pd_id=product.pd_id
其它解答:
1、update b set expression=(select expression from a where a.pd_ID=b.pd_ID) where exists(select 1 from a where a.pd_ID=b.pd_ID)
2、create table A(pd_ID int,name varchar(10),expression varchar(10))
insert A
select 1,'test1','abc' union all
select 2,'test2','asdf' union all
select 3,'test3','zxcv'
select * from A
create 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 B
begin 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 tran
drop table A,B