标题@oracle中连表update的问题
前言
近日,在项目中遇到一个需求。需要批量跑sql脚本。并且在脚本中,需要将A表中的数据根据B表的条件进行批量更新。但是,oracle中不支持update中使用left join等。
原因
Oracle没有update from语法,set前面只能存在一个表,逻辑只能在子查询中处理。
实现办法
1.子查询
写法一:
UPDATE table_1 a
SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),
col_x2 = (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m) ;
写法二:
UPDATE table_1 a
SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m);
注意:
- 对于子查询的值只能是一个唯一值,不能是多值。
- 子查询在绝大多数情况下,最后面的where EXISTS子句是重要的,否则将得到错误的结果。且where EXISTS子句可用另一方法代替,如上。最后的子句是对a表被更新记录的限制,如无此句,对于a表中某记录,如在b表中关联不到对应的记录,则该记录被更新字段将被更新为null。where EXISTS子句就是排除对a表中该情况的记录进行更新。
2.利用视图
UPDATE (SELECT A.NAME ANAME,B.NAME BNAME FROM A,B WHERE A.ID=B.ID)
SET ANAME=BNAME;
注意:
- 对于视图更新的限制:
如果视图基于多个表的连接,那么用户更新(update)视图记录的能力将受到限制。除非update只涉及一个表且视图列中包含了被更新的表的整个主键,否则不能更新视图的基表。
3.存储过程
MERGE INTO T2
USING T1
ON (T2.A = T1.A)
WHEN MATCHED THEN
UPDATE SET T2.C = T1.B
另外,Oracle中的Delete的from子句也没有多表联接的功能,只能通过子查询的方式来做:
delete from 表A where exists (select * from 表B where 表A.empid=表B.empid)
delete from 表A where 表A.empid in (select empid from 表B)