模拟表AA(ID,NAME_A,DEPT_A) 模拟表BB(ID,NAME_B,DEPT_B)
1.update单个字段(注意Oracle与其他数据库不一致,需要多限制一个条件,否则将不匹配的都刷为null)
update AA a set a.NAME_A = (select b.NAME_B from BB b where a.ID=b.ID) where exists (select 1 from BB b where a.ID=b.ID)
2.update多个字段
update AA a set (a.NAME_A,a.DEPT_A) = (select b.NAME_B,b.DEPT_B from BB b where a.ID=b.ID) where exists (select 1 from BB b where a.ID=b.ID)
3.merge into
MERGE INTO AA a
USING (SELECT b.ID,b.NAME_B,b.DEPT_B from BB b) c
ON (a.ID=c.ID )
WHEN MATCHED THEN
UPDATE SET a.NAME_A=c.NAME_B,a.DEPT_A=c.DEPT_B
--第二个比第一个更好,特别是针对行级触发器
MERGE INTO AA a
USING (SELECT b.ID,b.NAME_B,b.DEPT_B from BB b group by b.ID,b.NAME_B,b.DEPT_B) c
ON (a.ID=