SQL Delete based on condition in join
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
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)
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