1.在做Oracle相关开发的时候,经常遇到类似的问题,利用表a的记录更新表b的对应的记
录,这时候需要注意,如果SQL写的不当,会导致,表a中key不存在于表b中的记录除key之外的fields更新成NULL。
(1)例:
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:
(2)会产生错误的UPDATE:
UPDATE rl_test_a a
SET(http://www.doczj.com/doc/fb67b28f680203d8ce2f2483.html,a.title)=(SELECT name,title
FROM rl_test_b b
where a.id= b.id);
Screen shot: