SQL 以另外一表做查询和更新

2024-12-02 17:52:47

推荐回答（1个）

回答1：

‍试过了，两个都可以成功。
方法一：Oracle数据库中的merge用法
本来是想都写在merge中的，但是merge操作之后，只有匹配的update操作才可以用delete where子句删除目标表中满足条件的行。所以就又另外写了delete。
merge into B
using A
on (A.id = B.pid)
when matched then
update set B.sn = a.sn commit;
delete from B where B.PID not in (select distinct id from a);
commit;

方法二：

update b
set (b.pid, b.sn) = (select a.id,a.sn from a where a.id = b.pid)
where b.pid in (select distinct id from a);
delete from B where B.PID not in (select distinct id from a);
commit;