delete from table with foreign keys to other tables

Question

I am trying to delete from a table(MyTable) that has a foreign key reference linking it to 4 other tables.

I needed to delete all the data MyTable that is referenced by Table1 and Table2, but NOT Table3 and Table4. I have already deleted the data in Table1 and Table2

I tried something like this:

delete from MyTable where ID NOT IN(SELECT MyTableID FROM Table1)
delete from MyTable where ID NOT IN(SELECT MyTableID FROM Table2)

But it obviously doesn't work because if it did it would inadvertently delete the data that Table2 references.

Is there a way to delete from a table where FKs aren't being referenced by certain tables?

Check out cascading deletes: http://stackoverflow.com/questions/6260688/sql-server-cascade-delete — Ste, Aug 13 '13 at 16:13
@Ste No, I think that only works the other way around. I believe OP wants to delete rows which has a PK which isn't referenced in the FK of other tables. — Bernhard Barker, Aug 13 '13 at 16:31
@Dukeling - Ah, my bad. Should have done more than skimmed the question. — Ste, Aug 13 '13 at 17:08

Tomas Creemers · Accepted Answer · 2013-08-13T16:35:05.747

(Rewritten answer to SQL Server syntax after some basic research and finding The DELETE statement in SQL Server.)

Use the multiple table syntax of the DELETE statement.

DELETE
  MyTable
FROM
  MyTable
  LEFT JOIN Table1 ON MyTable.ID = Table1.MyTableID
  LEFT JOIN Table2 ON MyTable.ID = Table2.MyTableID
  LEFT JOIN Table3 ON MyTable.ID = Table3.MyTableID
  LEFT JOIN Table4 ON MyTable.ID = Table4.MyTableID
WHERE
  (Table1.MyTableID IS NOT NULL OR Table2.MyTableID IS NOT NULL)
  AND Table3.MyTableID IS NULL
  AND Table4.MyTableID IS NULL

The DELETE will only operate on the table before the FROM clause. You can select rows using other tables in the FROM clause which will not be affected. This example joins MyTable with all the tables that you mention and then checks for each row that either Table1 or Table2 refer to the row and that Table3 and Table4 do not refer to the row.

@PaulWilliams: thanks for the heads up on the added tag. I have rewritten (changed two lines) in the query to make it work on SQL Server (which is incidentally still valid MySQL syntax as well). — Tomas Creemers, Aug 13 '13 at 16:36

Bernhard Barker · Answer 2 · 2013-08-13T16:34:47.040

Probably not too efficient, but the below should work.

It should "delete all the data [in] MyTable that is referenced by Table1 and Table2, but NOT Table3 and Table4". Your query seems to not exactly match up with desiring to do this.

DELETE FROM MyTable
WHERE
  ID IN (SELECT MyTableID FROM Table1) AND
  ID IN (SELECT MyTableID FROM Table2) AND
  ID NOT IN (SELECT MyTableID FROM Table3) AND
  ID NOT IN (SELECT MyTableID FROM Table4)

score 0 · Answer 3 · answered Aug 13 '13 at 16:31

this is @ThomaCreemers 's convverted to SQL Server syntax

DELETE MyTable
FROM MyTable
  LEFT JOIN Table1 ON MyTable.ID = Table1.MyTableID
  LEFT JOIN Table2 ON MyTable.ID = Table2.MyTableID
  LEFT JOIN Table3 ON MyTable.ID = Table3.MyTableID
  LEFT JOIN Table4 ON MyTable.ID = Table4.MyTableID
WHERE
  (Table1.MyTableID IS NOT NULL OR Table2.MyTableID IS NOT NULL)
  AND Table3.MyTableID IS NULL
  AND Table4.MyTableID IS NULL

score 0 · Answer 4 · answered Aug 13 '13 at 16:32

0

This should work:

delete from MyTable
where 1=1
    AND ID NOT IN(SELECT MyTableID FROM Table1)
    AND ID NOT IN(SELECT MyTableID FROM Table2)

answered Aug 13 '13 at 16:32

bastos.sergio

6,684
4
26
36

delete from table with foreign keys to other tables

4 Answers4