当需要用一个表里的记录去更新另一个表是,往往容易陷入update语句语法误区,请注意下面例子:
create table TABLEA
(
ID NUMBER,
NAME VARCHAR2(100)
) ;
prompt Creating TABLEB...
create table TABLEB
(
ID NUMBER,
NAME VARCHAR2(1111)
) ;
insert into TABLEA (ID, NAME) values (1, 'A1');
insert into TABLEA (ID, NAME) values (2, 'A2');
insert into TABLEA (ID, NAME) values (3, 'A3');
insert into TABLEA (ID, NAME) values (4, 'A4');
commit;
insert into TABLEB (ID, NAME) values (1, 'B1');
insert into TABLEB (ID, NAME) values (2, 'B2');
commit;
错误写法:
UPDATE TABLEA SET
TABLEA.NAME=(select tableb.name from tableb where tablea.id=tableb.id);
select * from tablea 结果为
id name
1 B1
2 B2
3 NULL
4 NULL
正确写法:
UPDATE TABLEA SET
TABLEA.NAME=(select tableb.name from tableb where tablea.id=tableb.id)
where exists (select tableb.name from tableb where tablea.id=tableb.id );
select * from tablea 结果为
id name
1 B1
2 B2
3 A3
4 A4
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/297293/viewspace-694855/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/297293/viewspace-694855/