CODEWITHSUNDEEP
sqloracle-database
Javier Pequeno
Javier Pequeno

Reputation:

Oracle Delete Rows Matching On Multiple Values

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

Answers (6)

DevelopersDevelopersDevelopers
DevelopersDevelopersDevelopers

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

João Marcus
João Marcus

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

Cornell
Cornell

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

kristof
kristof

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

Gary Myers
Gary Myers

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

Tony Andrews
Tony Andrews

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

Related Questions