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.

Answer 1

I often do things like the following made-up example. (This example is from Informix SE running on Linux.)

The point 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 != null means 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 -- are 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.

Answer 2

Since the OP does not ask for a specific DB, it is better to use a standard compliant statement. Only MERGE is in SQL standard for deleting (or updating) rows while joining something on the 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 semantics, which protects from some error cases that 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.

Answer 3

delete
    table1
from 
    t2
where
    table1.ID=t2.ID

Works on mssql

Answer 4

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.

Answer 5

PostgreSQL implementation would be:

DELETE FROM t1
USING t2
WHERE t1.id = t2.id;

Answer 6

This will delete all rows in Table1 that match the criteria:

DELETE Table1 
FROM Table2 
WHERE Table1.JoinColumn = Table2.JoinColumn And Table1.SomeStuff = 'SomeStuff'

Answer 7

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.

Answer 8

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.

Answer 9

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.

Answer 10

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;

Answer 11

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)

Answer 12

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
)

Answer 13

Referencing MSDN T-SQL DELETE (Example D):

DELETE FROM Table1
FROM Tabel1 t1
   INNER JOIN Table2 t2 on t1.ID = t2.ID

Answer 14

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

Answer 15

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;