CODEWITHSUNDEEP
sql
WoltjerD
WoltjerD

Reputation: 463

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

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

Upvotes: 25

Views: 132984

Answers (3)

onedaywhen
onedaywhen

Reputation: 57043

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;

Upvotes: 4

Sklivvz
Sklivvz

Reputation: 31143

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.

Upvotes: 15

DavidEG
DavidEG

Reputation: 5957

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

Upvotes: 23

Related Questions