Reputation:
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.
Upvotes: 32
Views: 112316
Reputation: 238
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!
Upvotes: 8
Reputation: 1608
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
)
Upvotes: 17
Reputation: 41
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.
If an attribute, such as major, may be null, and you want null = null to be true, try:
DELETE
FROM student
WHERE (student.course, NVL(student.major,'sOmeStRinG') )
IN (SELECT schedule.course, NVL(schedule.major,'sOmeStRinG') FROM schedule)
Upvotes: 4
Reputation: 53854
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
Upvotes: 3
Reputation: 35401
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);
Upvotes: 3
Reputation: 132710
No, you just need parentheses:
DELETE FROM student WHERE
(student.course, student.major) IN
(SELECT schedule.course, schedule.major FROM schedule)
Upvotes: 58