Oracle删除行匹配多个值

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运算符中使用一列。真的吗？看起来像这样的查询应该是可能的。

6 个解决方案

#1

No, you just need parentheses:

不，你只需要括号：

DELETE FROM student WHERE
(student.course, student.major) IN
(SELECT schedule.course, schedule.major FROM schedule)

#2

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
)

#3

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子句中围绕您的条款添加parens。干杯!

#4

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

#5

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.

请注意，如果任何属性为null，则该行被视为不是IN。也就是说，如果课程相同且学生和计划专业都为空，则不会删除行。

If an attribute, such as major, may be null, and you want null = null to be true, try:

如果某个属性（如major）可能为null，并且您希望null = null为true，请尝试：

DELETE
FROM student
WHERE (student.course, NVL(student.major,'sOmeStRinG') )
IN (SELECT schedule.course, NVL(schedule.major,'sOmeStRinG') FROM schedule)

#6

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

#1