sql问题 A表 B表 查出两表都有的记录 并把B的某个字段记录赋值给A

Question

如题所述

Answer 1

假设A表ID与B表ID相同即认为A，B都存在
将b表NAME赋予A表NAME中

查询A表 B表 查出两表都有的记录
select * from A
where exists
(select 1 from B where A.ID= B.ID)

修改赋值
update A
set A.name= (select b.name from B where A.ID= B.ID)
where exists
(select 1 from B where A.ID= B.ID)追问

select * from yp_zb
where exists
(select * from yp_by_re where yp_zb.yp_num = yp_by_re.yp_num)
update yp_zb
set yp_zb.re_date= (select * from yp_by_re where yp_zb.re_date= yp_by_re.d_date)
where exists
(select * from yp_by_re where yp_zb.yp_num= yp_by_re.yp_num)

当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。

追答

update yp_zb
set yp_zb.re_date= (select * from yp_by_re where yp_zb.yp_num= yp_by_re.yp_num)
where exists
(select * from yp_by_re where yp_zb.yp_num= yp_by_re.yp_num)
不能yp_zb.re_date= yp_by_re.d_date
yp_zb.yp_num= yp_by_re.yp_num才是关键

Answer 2

update t1
set A表字段=B表的某个字段
from A T1,
B t2.
where
t1.字段=t2.字段追问

update yp_zb
set re_date=d_date
from yp_zb,yp_by_re
where
yp_zb.yp_num=yp_by_re.yp_num

还是出错~有哪里不对吗？

追答

update yp_zb
set re_date=yp_by_re.d_date--这里要加上第二个表的名字
from yp_zb,yp_by_re
where
yp_zb.yp_num=yp_by_re.yp_num