Delete all records in table which have no reference in another table

Question

I have a table which is called Document.

Document:

id int
docuid int
doc blob

Then I have two referencing tables

AppRequiredDocuments:

id int
appid int
docid int -> references document -> id

AppDocuments:

id int
appid int
docid int -> references document -> id

I have due to an very old migration orphaned items in the document table which have to references in the other tables. How can I delete only the documents in the document table which are not referenced in AppDocuments or AppRequriedDocuments?

Answer 1

One approach uses a delete join:

DELETE d
FROM Document d
LEFT JOIN AppRequiredDocuments t1
  ON d.id = t1.docid
LEFT JOIN AppDocuments t2
  ON d.id = t2.docid
WHERE t1.docid IS NULL AND
      t2.docid IS NULL

The logic here is that if a given Document record is not referenced by anything in the two auxiliary tables, then in the result set of the join the docid columns for those two other tables should both be NULL.