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);