0

I have a table that has a bunch of dependencies, but most records in there actually aren't referenced elsewhere. I would like to issue a delete command but for it to not fail if it encounters such records - just delete the ones without dependencies and leave others intact.

This delete command will be called from my ASP.NET web application and in the future this table might have additional dependencies or structural changes. And I wouldn't want to have to change the delete command each time.

What's the fastest way to do this?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Riz
  • 6,486
  • 19
  • 66
  • 106
  • 1
    You need to write a query to find all records that have references and exclude them from your delete. That's the only way. – Alex Jun 30 '20 at 04:01
  • Does this answer your question? [How to delete rows in tables that contain foreign keys to other tables](https://stackoverflow.com/questions/3656099/how-to-delete-rows-in-tables-that-contain-foreign-keys-to-other-tables) – Muhammad Vakili Jun 30 '20 at 04:03

2 Answers2

1

The way to do that is to only attempt to delete records with no referencing records e.g.

delete
from MyTableA
where not exists (select 1 from MyTableB B where B.MyTableAID = MyTableA.MyTableAID)
-- etc for all referencing tables

If you put this inside a stored procedure its relatively easy to update whenever you update your database structure without needing to modify your code.

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • i would like to avoid this as this script will be called from my asp.net web application and in the future this table might have additional dependencies or structural changes. And I wouldn't want to have to change this each time. – Riz Jun 30 '20 at 04:03
  • @Riz there isn't any other (easy) way to do it - you could write dynamic SQL which first queries the existing referential relationships and then build a similar query to above, but thats not how SQL is intended to work. – Dale K Jun 30 '20 at 04:07
  • @Riz If you want automatic deletion/cleanup, then use cascading deletion. If you _can't_ do that, then this answer is probably the most exact way to describe the logic you want here +1. – Tim Biegeleisen Jun 30 '20 at 04:11
  • 1
    Thanks, I'll take your word for it and mark this as answer since it gets me closest to what I want. – Riz Jun 30 '20 at 04:25
1

Just as an add-on to Dale's answer, we could also phrase the delete using an inner join within a CTE. Then, delete from that CTE:

WITH cte AS (
    SELECT a.*
    FROM MyTableA a
    LEFT JOIN MyTableB b ON a.MyTableAID = b.MyTableAID
    WHERE b.MyTableAID IS NULL
)

DELETE
FROM cte;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • i would like to avoid this as this script will be called from my asp.net web application and in the future this table might have additional dependencies or structural changes. And I wouldn't want to have to change this each time – Riz Jun 30 '20 at 04:05
  • You never mentioned this in your original question. The answer I gave is very reasonable for a general SQL Server solution. – Tim Biegeleisen Jun 30 '20 at 04:06
  • @TimBiegeleisen is there a benefit to do it this way? Just curious :) – Dale K Jun 30 '20 at 04:08
  • 1
    @DaleK The syntax for a delete join in SQL Server can be a bit convoluted (at least it is for me). The delete from CTE option lets you write basic select SQL which can then be turned around and applied to do a delete. – Tim Biegeleisen Jun 30 '20 at 04:09
  • this actually seems to delete records which HAVE dependencies, whereas I want to skip the deletion of such records. – Riz Jun 30 '20 at 04:24
  • @Riz You're right, sorry, I meant to use a left anti-join. – Tim Biegeleisen Jun 30 '20 at 04:25