Oracle 用一个表的列更新另一个表对应的记录
在做Oracle相关开发的时候，经常遇到类似的问题，利用表a的记录更新表b的对应的记录，这时候需要注意，如果SQL写的不当，会导致，表a中key不存在于表b中的记录除key之外的fields更新成NULL。
例：
DROP TABLE rl_test_a;
CREATE TABLE rl_test_a
(
id integer PRIMARY KEY,
name varchar2(30),
title varchar2(30)
);
DROP TABLE rl_test_b;
CREATE TABLE rl_test_b
(
id integer PRIMARY KEY,
name varchar2(30),
title varchar2(30)
);
begin
INSERT INTO rl_test_a VALUES(1,'Jack','SE');
INSERT INTO rl_test_a VALUES(2,'Bill','SSE');
INSERT INTO rl_test_b VALUES(1,'Jack','PL');
INSERT INTO rl_test_b VALUES(3,'Dennis','TL');
COMMIT;
end;
Screen shot：
会产生错误的UPDATE:
UPDATE rl_test_a a
SET(a.name,a.title) = (SELECT name,title
FROM rl_test_b b
where a.id = b.id);
Screen shot:
由此可以看到，发生了错误，在rl_test_b中不存在的（2,'Bill','SSE')被更新成了(2,NULL,NULL)，这显然是违背我们的需求的。
正确的写法：
ROLLBACK;
UPDATE rl_test_a a
SET(a.name,a.title) = (SELECT name,title
FROM rl_test_b b
where a.id = b.id)
WHERE a.id IN (SELECT b.id FROM rl_test_b b);
或者
UPDATE rl_test_a a
SET(a.name,a.title) = (SELECT name,title
FROM rl_test_b b
where a.id = b.id)
WHERE EXISTS (SELECT 1 FROM rl_test_b b WHERE a.id = b.id);
Screen shot1:
Screen shot2：
总结：
在实现用一个表的fields 更新另一个表对应的记录的时候：
1. In Mysql, user is able to use following command:
UPDATE tbl1 a, tbl2 b SET a.col1=b.col1, a.col2=b.col2 WHERE a. key=b.key
Note: It is wrong in Oracle.
2. In Oracle, user is able to use following command:
UPDATE tbl1 a
SET (a.col1, a.col2) = (SELECT b.col1, b.col2
FROM tbl2 b
WHERE a.key = b.key)
WHERE a.key in (select key from tbl2)
OR
UPDATE tbl1 a
SET (a.col1, a.col2) = (SELECT b.col1, b.col2
FROM tbl2 b
WHERE a.key = b.key)
WHERE EXISTS (SELECT 1 FROM tbl2 b WHERE a.key = b.key)
Note:
1) It is valid in Oracle while invalid in Mysql.
2) Where a.key in (select key from tbl2)/EXISTS is must, or else, when tbl1.key does not exist in tbl2.key, tbl1.col1 and tbl1.col2 will be updated to Null, which is not expected.
1.jpg来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24025515/viewspace-719029/ ，如需转载，请注明出处，否则将追究法律责任。
