在 Oracle 10g 中使用左连接删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3672285/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete with Left Join in Oracle 10g
提问by Simon T.
I have the following code that works fine in MS SQL Server:
我有以下代码在 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
Basically, I want to delete all occurrence that can be found in grpand 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:
基本上，我想删除可以在 中找到grp并且没有任何等效项的所有事件in 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
A INclause 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?
一个IN子句会，如果我没有作品多次按键，但我不明白我怎么能和我的数据使用。那么，替代方案是什么？
回答by Shannon Severance
Tables and data:
表格和数据：
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.
Using in. NoteDo not use if the IDs in the subquery can be null. Not inof nullnever returns true.
使用in. 注意如果子查询中的 ID 可以是 ，则不要使用null。Not inofnull永远不会返回 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
Using exists
使用 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>
回答by Vincent Malgrat
Shannon's solutionis the way to go: use the operator NOT IN (or NOT EXISTS).
香农的解决方案是要走的路：使用运算符 NOT IN（或 NOT EXISTS）。
You can however delete or update a join in Oracle, but the synthax is not the same as MS SQL Server:
但是，您可以在 Oracle 中删除或更新连接，但合成器与 MS SQL Server 不同：
SQL> DELETE FROM (SELECT grp.*
2 FROM grp
3 LEFT JOIN my_data ON grp.id1 = my_data.id1
4 AND grp.id2 = my_data.id2
5 AND grp.id3 = my_data.id3
6 AND grp.id4 = my_data.id4
7 WHERE my_data.id1 IS NULL);
2 rows deleted
Additionally, Oracle will only let you update a join if there is no ambiguity as to which base row will be accessed by the statement. In particular, Oracle won't risk an update or a delete (the statement will fail) if there is a possibility that a row may appear twice in the join. In this case, the delete will only work if there is a UNIQUE constraint on my_data(id1, id2, id3, id4).
此外，如果语句将访问哪个基行没有歧义，Oracle 只会让您更新连接。特别是，如果行在连接中可能出现两次，Oracle 不会冒险进行更新或删除（语句将失败）。在这种情况下，删除仅在存在 UNIQUE 约束时才起作用my_data(id1, id2, id3, id4).
回答by Nick Pierpoint
If you want to ensure there is no ambiguity in what's being deleted, you could change Vincent's solutionto:
如果您想确保删除的内容没有歧义，您可以将Vincent 的解决方案更改为：
delete from grp where rowid in
(
select
grp.rowid
from
grp left outer 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
)
回答by Artisan
I can't add a comment because it need 50 reps,so I add a answer here.
我无法添加评论，因为它需要 50 次重复，所以我在这里添加了一个答案。
I tested Vincent's delete from query, that syntax can't let you delete what you want,at least it's not a common use for all the delete join cases.
我测试了 Vincent 从查询中删除，该语法不能让您删除您想要的内容，至少它不是所有删除连接情况的常见用法。
At first I create a table using oracle default user scott:
首先我使用 oracle 默认用户 scott 创建一个表：
create table emp1 as select * from emp where sal<2000;
I want to delete the records from emp where empno in emp1(just a simple test),so I used this delete from query:
我想从 emp 中删除记录，其中 empno 在 emp1 中（只是一个简单的测试），所以我使用了从查询中删除：
delete from (select a.* from emp a join emp1 b on a.empno=b.empno);
No matter what the table or join order is,left join or inner join,no matter what where clause I use,the sql will delete the corresponding records in emp1.
无论是什么表或连接顺序，左连接还是内连接，无论我使用什么where子句，sql都会删除emp1中的相应记录。
So I think this delete from query can not let you delete from a specified table. Loop a cursor will be a better way for these cases.
所以我认为这个从查询中删除不能让你从指定的表中删除。对于这些情况，循环游标将是更好的方法。
