在Oracle 10g中行使左毗连删除

我有以下代码在MS SQL Server中事变正常：

delete grp
from grp
left join my_data
on grp.id1 = my_data.id1
and grp.id2 = my_data.id2
and grp.id3 = my_data.id3
and grp.id4 = my_data.id4
where my_data.id1 is NULL

根基上,我想删除在grp中可以找到的全部变乱,而且在my_data中没有任多么价性.不幸的是,它在Oracle 10g中不起浸染.我实行行使旧的语法左毗连(),但它也不事变.喜好这个：

delete grp
from grp,my_data
where grp.id1 = my_data.id1 (+)
and grp.id2 = my_data.id2 (+)
and grp.id3 = my_data.id3 (+)
and grp.id4 = my_data.id4 (+)
and my_data.id1 is NULL

假如我没有多个密钥,可是我看不到怎样行使我的数据,则IN子句将起浸染.那么什么步伐呢？

SQL> create table grp (id1 number null,id2 number null,id3 number null,id4 number null);    
Table created.

SQL> create table my_data (id1 number null,id4 number null);

Table created.

SQL> insert into grp values (1,2,3,4);

1 row created.

SQL> insert into grp values (10,20,30,40);

1 row created.

SQL> insert into grp values (1,40);

1 row created.

SQL> insert into my_data values (1,4);

1 row created.

SQL> commit;

Commit complete.

行使in.留意假如子查询中的ID可觉得空,请勿行使.不在null不会返回true.

SQL> delete grp where (id1,id2,id3,id4) not in (select id1,id4 from my_data);

2 rows deleted.

SQL> select * from grp;

       ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
         1          2          3          4

行使存在

SQL> rollback;

Rollback complete.

SQL> delete grp where not exists (select * from my_data where grp.id1 = my_data.id1 and grp.id2 = my_data.id2 and grp.id3 = my_data.id3 and grp.id4 = my_data.id4);

2 rows deleted.

SQL> select * from grp;

       ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
         1          2          3          4

SQL>

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!