在Oracle 10g中使用左联接删除 [英] Delete with Left Join in Oracle 10g
问题描述
我有以下代码可以在MS SQL Server中正常工作:
I have the following code that works fine in 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
中可以找到的所有匹配项,并且没有任何等价物in my_data
.可悲的是,它在Oracle 10g中不起作用.我尝试对左联接(+)使用旧语法,但是它也不起作用.像这样:
Basically, I want to delete all occurrence that can be found in grp
and don't have any equivalence in my_data
. Sadly, it doesn't work in Oracle 10g. I tried using the old syntax for left join (+) but it doesn't work either. Like this:
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
子句将起作用.那么,还有什么选择呢?
A IN
clause would works if I didn't have multiple keys but I don't see how I could use it with my data. So, what is the alternative?
推荐答案
表和数据:
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, id2 number null, id3 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, 2, 30, 40);
1 row created.
SQL> insert into my_data values (1, 2, 3, 4);
1 row created.
SQL> commit;
Commit complete.
使用in
. 注意如果子查询中的ID可以为null
,请不要使用. null
中的Not in
从不返回true.
Using in
. Note Do not use if the IDs in the subquery can be null
. Not in
of null
never returns true.
SQL> delete grp where (id1, id2, id3, id4) not in (select id1, id2, id3, id4 from my_data);
2 rows deleted.
SQL> select * from grp;
ID1 ID2 ID3 ID4
---------- ---------- ---------- ----------
1 2 3 4
使用exists
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>
这篇关于在Oracle 10g中使用左联接删除的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!