ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 用一个表的列更新另一个表对应的记录

Oracle 用一个表的列更新另一个表对应的记录

原创 Linux操作系统 作者:l476861438 时间:2012-03-19 15:24:45 0 删除 编辑

在做Oracle相关开发的时候,经常遇到类似的问题,利用表a的记录更新表b的对应的记录,这时候需要注意,如果SQL写的不当,会导致,表akey不存在于表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

2.jpg

3.jpg

4.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24025515/viewspace-719029/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2012-03-15

  • 博文量
    8
  • 访问量
    13396