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.

score 54 · Accepted Answer · answered Feb 02 '09 at 17:32

54

No, you just need parentheses:

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

answered Feb 02 '09 at 17:32

Tony Andrews

129,880
21
220
259

1

So this is actually misleading and I would argue essentially incorrect for how deeply non-intuitive it is. If your row contains null value IN does not work. Which, I guess, makes sense for how nulls are, but is completely inconsistent with, for example, how minus works. – MK. Jul 31 '15 at 04:39

score 17 · Answer 2 · edited Feb 03 '15 at 13:34

17

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
)

edited Feb 03 '15 at 13:34

Ram Sharma

8,676
7
43
56

answered Feb 03 '09 at 11:17

João Marcus

1,610
1
13
20

5

+1; WHERE EXISTS is a woefully underrated mechanism, much better than use of IN – Brian May 17 '10 at 14:43
1

@Brian: (I realise I'm a bit late responding to your comment!) Why do you say EXISTS is much better than IN? – Tony Andrews Mar 16 '12 at 10:08
1

A very good question. My understanding is that it's a good choice in performance terms, however I've little to offer in terms of evidence. That said, I've managed to find this: http://www.techrepublic.com/article/oracle-tip-understand-the-difference-between-in-and-exists-in-subqueries/5297080 – Brian Mar 16 '12 at 10:14
1

Other, probably more helpful, links: http://www.techonthenet.com/sql/exists.php and http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql – Brian Mar 16 '12 at 10:18
1

Well there is also a potential to have too many result values in the IN clause which I have seen cause problems in the past... like more than 1024.... – Allen Oct 31 '14 at 16:46

score 7 · Answer 3 · edited Feb 03 '15 at 13:34

7

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!

edited Feb 03 '15 at 13:34

Ram Sharma

8,676
7
43
56

answered Feb 02 '09 at 17:32

DevelopersDevelopersDevelopers

228
2
6

score 3 · Answer 4 · edited Apr 06 '12 at 11:52

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)

score 3 · Answer 5 · answered Feb 02 '09 at 22:19

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

kristof · Answer 6 · 2009-02-03T10:42:46.413

2

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

edited Feb 03 '09 at 10:42

answered Feb 02 '09 at 17:37

kristof

52,923
24
87
110

No, standard SQL supports only one table in DELETE. MySQL and Microsoft SQL Server support multi-table DELETE as an extension to the standard, but Oracle does not. – Bill Karwin Feb 02 '09 at 18:04
The SQL standard concept of a table isn't strictly limited to a table. It often includes views. Certain views are updatable, and by extension, deletable. – Gary Myers Feb 02 '09 at 22:16
Thanks for the comments, it is good to learn about differences in DB implementations. I have updated my answer to include your points – kristof Feb 03 '09 at 10:45

Oracle Delete Rows Matching On Multiple Values

6 Answers6

Linked