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

Answer 1

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

Answer 2

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.

Answer 3

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;