SQLSERVER / ORACLE 中delete/update 中的別名使用

SQLSERVER / ORACLE   中delete/update  中的別名使用

update/delete 語句往往是帶有條件的, 這些條件如果和其他表有關聯,此時就會在更新語句中使用別名. SQLSERVER 中,對update/delete 語句別名支援很好,用法和和select 接近. 然而ORACLE中,不支援多表關聯update/delete, 這樣我們只能通過在update/delete 語句後面加where條件來實現.

1 sql server 示例

update

UPDATE t1 SET t1.ETITLE ='1' FROM dbo.PROPOSAL t1,dbo.PROPOSAL_CACHED t2 WHERE t1.POS_CODE = t2.POS_CODE AND 1=0;

delete

DELETE t1 FROM dbo.PROPOSAL_CACHED t1,dbo.PROPOSAL t2 WHERE t1.POS_CODE = t2.POS_CODE AND 1=0;

2 oracle 表中

資料構建

create table zk_test1(
en_name varchar(10),
primary key (en_name)
);
create table zk_test2(
en_name varchar(10),
primary key (en_name)
);
insert into zk_test1(en_name) values('123');
insert into zk_test1(en_name) values('456');
insert into zk_test2(en_name) values('456');
insert into zk_test2(en_name) values('789');

資料展示
這裡寫圖片描述

以下會報錯

update t1 set t1.en_name = '456' from zk_test1 t1,zk_test2 t2 where t1.en_name = t2.en_name;

使用exists,曲線做法如下

update

update zk_test1 t1  set  en_name = '456'  where exists (select 1 from zk_test2 t2 where t1.en_name = t2.en_name);

delete

delete from zk_test1 t1  where exists (select 1 from zk_test2 t2 where t1.en_name = t2.en_name);