SQL Oracle 删除匹配多个值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/504060/
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
Oracle Delete Rows Matching On Multiple Values
提问by Tony Andrews
I want to do something like:
我想做类似的事情：
DELETE FROM student WHERE
student.course, student.major IN
(SELECT schedule.course, schedule.major FROM schedule)
However, it seems that you can only use one column with the IN operator. Is that true? Seems like a query like this should be possible.
但是，似乎只能将一列与 IN 运算符一起使用。真的吗？看起来像这样的查询应该是可能的。
回答by Tony Andrews
No, you just need parentheses:
不，你只需要括号：
DELETE FROM student WHERE
(student.course, student.major) IN
(SELECT schedule.course, schedule.major FROM schedule)
回答by Jo?o Marcus
You could also use the EXISTS clause:
您还可以使用 EXISTS 子句：
DELETE FROM student WHERE
EXISTS
(
SELECT 1 FROM schedule
WHERE schedule.course=student.course
AND schedule.major=student.major
)
回答by DevelopersDevelopersDevelopers
DELETE FROM student WHERE
(student.course, student.major) IN
(SELECT schedule.course, schedule.major FROM schedule)
Put parens around your terms in the where clause. Cheers!
在 where 子句中将括号括起来。干杯!
回答by Gary Myers
In Oracle, you can do a delete from an in-line view, but it generally needs a foreign key that ensures that a row from the table from which the row is deleted cannot be represented by more than one row in the view.
在 Oracle 中，您可以从内联视图中执行删除操作，但它通常需要一个外键，以确保删除行的表中的行不能由视图中的多行表示。
create table parent (id number primary key);
create table child (id number primary key, parent_id number references parent);
insert into parent values(1);
insert into child values(2,1);
delete from (select * from parent p, child c where c.parent_id = p.id);
回答by Cornell
Note that if any attributes are null, the row's considered not IN. That is, if courses are equal and both student and schedule major are null, row will not be deleted.
请注意，如果任何属性为空，则认为该行不是 IN。也就是说，如果课程相等并且学生和计划专业都为空，则不会删除行。
If an attribute, such as major, may be null, and you want null = null to be true, try:
如果某个属性（例如主要）可能为 null，并且您希望 null = null 为 true，请尝试：
DELETE
FROM student
WHERE (student.course, NVL(student.major,'sOmeStRinG') )
IN (SELECT schedule.course, NVL(schedule.major,'sOmeStRinG') FROM schedule)
回答by kristof
The syntax below works in SQLServer but I believe it is a standard sql but as pointed out in comments this is non standard implementation and is not currently supported in Oracle.
下面的语法适用于 SQLServer，但我相信它是一个标准的 sql，但正如评论中指出的那样，这是非标准实现，目前在 Oracle 中不受支持。
I will leave it for reference
我将其留作参考
delete s
from
student s
inner join schedule sch
on s.course=sch.course
and s.major = sch.major
