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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] DELETE ... FROM ... WHERE ... IN

Examples related to sql