Delete all rows in a table based on another table

Question

I can t seem to ever remember this query   I want to delete all rows in table1 whose ID s are the same as in Table2   So   DELETE table1 t1  WHERE t1 ID   t2 ID   I know I can do a WHERE ID IN  SELECT ID FROM table2  but I want to do this query using a JOIN if possible

User · Answer

While the OP doesn t want to use an  in  statement  in reply to Ankur Gupta  this was the easiest way I found to delete the records in one table which didn t exist in another table  in a one to many relationship   DELETE FROM Table1 as t1 WHERE ID Number NOT IN  SELECT ID Number FROM Table2 as t2    Worked like a charm in Access 2016  for me

User · Answer

There is no solution in ANSI SQL to use joins in deletes  AFAIK   DELETE FROM Table1 WHERE Table1 id IN  SELECT Table2 id FROM Table2    Later edit  Other solution  sometimes performing faster    DELETE FROM Table1 WHERE EXISTS  SELECT 1 FROM Table2 Where Table1 id   Table2 id

User · Answer

I often do things like the following made-up example   This example is from Informix SE running on Linux    The point of of this example is to delete all real estate exemption abatement transaction records -- because the abatement application has a bug -- based on information in the real estate table    In this case last update    nullmeans the account is not closed  and res exempt     p  means the accounts are not personal property  commercial equipment furnishings    delete from trans  where   yr    16  and     tran date    01 22 2016  and     acct type    r  and     tran type    a  and     bill no in  select acct no from real estate where last update is not null  and res exempt     p      I like this method  because the filtering criteria -- at least for me -- is easier to read while creating the query  and to understand many months from now when I m looking at it and wondering what I was thinking

User · Answer

Found this link useful   Copied from there  Oftentimes  one wants to delete some records from a table based on criteria in another table  How do you delete from one of those tables without removing the records in both table   DELETE DeletingFromTable      FROM DeletingFromTable INNER JOIN CriteriaTable      ON DeletingFromTable field id   CriteriaTable id      WHERE CriteriaTable criteria    value     The key is that you specify the name of the table to be deleted from as the SELECT  So  the JOIN and WHERE do the selection and limiting  while the DELETE does the deleting  You re not limited to just one table  though  If you have a many-to-many relationship  for instance  Magazines and Subscribers  joined by a Subscription  and you re removing a Subscriber  you need to remove any potential records from the join model as well        DELETE subscribers  subscriptions      FROM subscribers INNER JOIN subscriptions         ON subscribers id   subscriptions subscriber id      INNER JOIN magazines         ON subscriptions magazine id   magazines id      WHERE subscribers name  Wes     Deleting records with a join could also be done with a LEFT JOIN and a WHERE to see if the joined table was NULL  so that you could remove records in one table that didn t have a match  like in preparation for adding a relationship   Example post to come

User · Answer

Referencing MSDN T-SQL DELETE  Example D    DELETE FROM Table1 FROM Tabel1 t1    INNER JOIN Table2 t2 on t1 ID   t2 ID

User · Answer

This is old I know  but just a pointer to anyone using this ass a reference  I have just tried this and if you are using Oracle  JOIN does not work in DELETE statements  You get a the following message   ORA-00933  SQL command not properly ended

User · Answer

PostgreSQL implementation would be   DELETE FROM t1 USING t2 WHERE t1 id   t2 id

User · Answer

This will delete all rows in Table1 that match the criteria   DELETE Table1  FROM Table2  WHERE Table1 JoinColumn   Table2 JoinColumn And Table1 SomeStuff    SomeStuff

User · Answer

To Delete table records based on another table       Delete From Table1 a Table2 b where a id b id      Or        DELETE FROM Table1     WHERE Table1 id IN  SELECT Table2 id FROM Table2     Or          DELETE Table1      FROM Table1 t1 INNER JOIN Table2 t2 ON t1 ID   t2 ID

User · Answer

DELETE t1  FROM Table1 t1 JOIN Table2 t2 ON t1 ID   t2 ID    I always use the alias in the delete statement as it prevents the accidental  DELETE Table1    caused when failing to highlight the whole query before running it

User · Answer

I think that you might get a little more performance if you tried this  DELETE FROM Table1 WHERE EXISTS     SELECT 1   FROM Table2   WHERE Table1 ID   Table2 ID

User · Answer

DELETE Table1 FROM Table1 INNER JOIN Table2 ON Table1 ID   Table2 ID

User · Answer

Try this   DELETE Table1 FROM Table1 t1  Table2 t2 WHERE t1 ID   t2 ID    or  DELETE Table1 FROM Table1 t1 INNER JOIN Table2 t2 ON t1 ID   t2 ID

User · Answer

Since the OP does not ask for a specific DB  better use a standard compliant statement  Only MERGE is in SQL standard for deleting  or updating  rows while joining something on target table   merge table1 t1     using           select t2 ID             from table2 t2       as d     on t1 ID   d ID     when matched then delete    MERGE has a stricter semantic  protecting from some error cases which may go unnoticed with DELETE     FROM  It enforces  uniqueness  of match   if many rows in the source  the statement inside using  match the same row in the target  the merge must be canceled and an error must be raised by the SQL engine

User · Answer

delete     table1 from      t2 where     table1 ID t2 ID   Works on mssql

[sql] Delete all rows in a table based on another table

Examples related to sql