SQL Delete based on condition in join

Question

It is possible to delete records based on a satisfied condition with a join query?

For instance, I have a linking table joining 3 records. The query I have at the moment deletes records from this table where one of the id's isn't IN() an imploded Php array. I've come to realise that the query should only remove records from this table if the id's don't exist in the array and they belong to a certain other table based on the a link to another table.

This is pretty abstract, an example (or the real thing) would be great. Otherwise, I'd fathom you can employ left outer joins and IS NULL for the right side to determine the records to be deleted. — 0xCAFEBABE, Nov 04 '10 at 13:48
You can't do DELETE FROM X JOIN Y on X.ID = Y.ID, if that's what you're asking. — dotariel, Nov 04 '10 at 13:54

Greg Dougherty · Answer 1 · 2011-04-27T21:41:03.623

For SQL Server, the command is slightly different:

DELETE FROM TableA
FROM TableA LEFT OUTER JOIN TableB ON TableA.Column = TableB.Column 
WHERE TableB.Column IS NULL

No, that's not a typo, yes, you do need "FROM TableA" twice. At least, you need the second FROM (the first is optional). The following has the advantage that it works for both SQL Server and MySQL:

DELETE TableA
FROM TableA LEFT OUTER JOIN TableB ON TableA.Column = TableB.Column 
WHERE TableB.Column IS NULL

score 15 · Accepted Answer · answered Nov 04 '10 at 14:10

15

I like to use EXISTS clauses for this:

DELETE FROM TableA
WHERE
  <<put your array condition here>> 
  AND NOT EXISTS 
  (SELECT 1 FROM TableB Where TableB.ID=TableA.ID)

answered Nov 04 '10 at 14:10

Bill

4,425
3
21
22

This is my preferred answer because: https://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join – ilcorvo Jun 19 '20 at 12:23

score 12 · Answer 3 · edited Feb 27 '15 at 10:56

You can use :

DELETE Based on a Join:

DELETE A      
FROM TableA AS A
  LEFT OUTER JOIN TableB As B ON A.Id = B.TabaleAId 
WHERE B.Column IS NULL

Delete With SubQuery:

DELETE 
FROM TableA AS A
Where
    A.id not in ( Select B.TabaleAId From Tab;eB As B )

or

DELETE FROM TableA
WHERE Not EXISTS 
  (
    SELECT * 
    FROM TableB As B
    Where B.TableAId = TableA.Id
   )

DELETE Using Table Expressions:

With A 
As
    (
        Select TableA.*
        FROM TableA AS A
            LEFT OUTER JOIN TableB As B ON A.Id = B.TabaleAId 
        WHERE B.Column IS NULL
    )
Delete From A

score 5 · Answer 4 · edited May 25 '17 at 02:09

5

DELETE FROM a
  FROM TableA AS a LEFT OUTER JOIN TableB AS b 
  on a.CALENDAR_DATE = b.CALENDAR_DATE AND a.ID = b.ID 
  Where b.ID is null

You can first use the select statement and verify your records that you want to delete and then remove the select statement and add Delete FROM tablename with the above query syntax.

edited May 25 '17 at 02:09

Pang

9,564
146
81
122

answered May 25 '17 at 02:02

Chetu P

83
1
6

score 4 · Answer 5 · answered Nov 04 '10 at 13:55

4

DELETE FROM TableA
LEFT OUTER JOIN TableB
WHERE TableB.Column IS NULL

Will delete the records in tableA that don't have a corresponding record in TableB. Is that like what you are after?

answered Nov 04 '10 at 13:55

JNK

63,321
15
122
138

Not sure about other databases but in MySql you definitely will need to do like DELETE TableA FROM TableA LEFT JOIN.. – Krishan Gopal Sep 02 '13 at 07:25

Vahid Farahmandian · Answer 6 · 2020-04-09T15:02:19.313

The easiest way to Delete based on join is as follow:

1.Write your query using SELECT statement instead of DELETE statement

  SELECT COLUMNS
    FROM    Table1
            INNER JOIN Table2  ON Table1.YYY = Table2.XXX

2.Replace SELECT COLUMNS with DELETE FROM TABLE

 DELETE  FROM Table1
    FROM    Table1
            INNER JOIN Table2 ON Table1.YYY = Table2.XXX

Note that we need to specify FROM twice, one for DELETE part and one for JOIN part.

score 1 · Answer 7 · answered Nov 04 '10 at 14:08

delete from TableA
where id in 
(
   select id from TableA
   except select id from TableB
)

Which means "delete from tableA where id in table a but not in table b)

Otherwise a Merge statement might help you (when matched/not matched delete etc) http://technet.microsoft.com/en-us/library/bb510625.aspx

SQL Delete based on condition in join

7 Answers7