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

You can use NOT EXISTS to find and delete those items:

delete from document d
where not exists (select 1 from AppRequiredDocuments a where a.docid = d.id);
and not exists (select 1 from AppDocuments a where a.docid = d.id);

Answer 2

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.

Answer 3

You could use the union [all] operator to generate a single column of references, and then check against it, e.g., with the [not] exists operator:

DELETE FROM Document d
WHERE  NOT EXISTS (SELECT *
                   FROM   AppRequiredDocuments ard
                   WHERE  ard.docid = d.id
                   UNION ALL
                   SELECT *
                   FROM   AppDocuments ad
                   WHERE  ad.docid = d.id)