DELETE ... FROM ... WHERE ... IN

Question

i'm looking for a way to delete records in table 1 with matching combinations in table 2 on 'stn' and 'jaar'. The contents of column 'jaar' in table2 is formatted in a previous stage/query by

year(datum) AS 'jaar'

Sorry, can't find again the site where i found this "solution".

DELETE FROM table1
WHERE stn, year(datum) IN (SELECT stn, jaar FROM table2);

Which database are you using? Specify it in tags of your question — sll, Nov 05 '11 at 12:19
That DELETE statement is not valid. When using the `IN` operator the sub-select may not return more than one column. It will generate an error in any sensible DBMS — , Nov 05 '11 at 12:34

score 23 · Accepted Answer · answered Nov 05 '11 at 13:27

23

You can achieve this using exists:

DELETE
  FROM table1
 WHERE exists(
           SELECT 1
             FROM table2
            WHERE table2.stn = table1.stn
              and table2.jaar = year(table1.datum)
       )

answered Nov 05 '11 at 13:27

DavidEG

5,857
3
29
44

Thank you very much DavidEG! It works! Saved me a lot of time ;-) – WoltjerD Nov 05 '11 at 17:39
I think I'm getting errors in SQL Server 2012 when I add a shorthand (i.e. `FROM table1 t1`). But I could have a completely different error – Zach Smith Aug 15 '17 at 12:18

Sklivvz · Answer 2 · 2012-11-19T09:59:51.687

15

The canonical T-SQL (SqlServer) answer is to use a DELETE with JOIN as such

DELETE o
FROM Orders o
INNER JOIN Customers c
    ON o.CustomerId = c.CustomerId
WHERE c.FirstName = 'sklivvz'

This will delete all orders which have a customer with first name Sklivvz.

edited Nov 19 '12 at 09:59

answered Nov 05 '11 at 13:39

Sklivvz

30,601
24
116
172

onedaywhen · Answer 3 · 2013-06-03T10:01:36.480

4

Try adding parentheses around the row in table1 e.g.

DELETE 
  FROM table1
 WHERE (stn, year(datum)) IN (SELECT stn, jaar FROM table2);

The above is Standard SQL-92 code. If that doesn't work, it could be that your SQL product of choice doesn't support it.

Here's another Standard SQL approach that is more widely implemented among vendors e.g. tested on SQL Server 2008:

MERGE INTO table1 AS t1
   USING table2 AS s1
      ON t1.stn = s1.stn
         AND s1.jaar = YEAR(t1.datum)
WHEN MATCHED THEN DELETE;

edited Jun 03 '13 at 10:01

answered Nov 07 '11 at 11:26

onedaywhen

55,269
12
100
138

The SQL-92 code is rejected by MS SQL 2008 with the error like "Incorrect syntax near ','", which points to the fragment right after WHERE :( – AntonK Sep 25 '13 at 17:02
@AntonK: Yes indeed, "your SQL product of choice doesn't support it." But _you can_ vote for it: http://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors – onedaywhen Oct 03 '13 at 12:32

DELETE ... FROM ... WHERE ... IN

3 Answers3

Linked