Oracle Delete Rows Matching On Multiple Values

Question

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.

Answer 1

No, you just need parentheses:

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

Answer 2

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!

Answer 3

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.

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

Answer 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.

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

Answer 5

You could also use the EXISTS clause:

DELETE FROM student WHERE EXISTS ( SELECT 1 FROM schedule WHERE schedule.course=student.course AND schedule.major=student.major )