SQL Server : Delete rows based on foreign key

Question

I'm facing a problem. Let say I have 3 tables :

A [ID_A]
B [ID_A, ID_C]
C [ID_C]

A classical many to many relationship.

I want to delete a row in C, and all the A associated WHERE the A are only associated with C. To be clear :

[a1, a2, a3]
[[a1, c1], [a2, c1], [a1, c2]
[c1, c2]

I want to delete c1 and a1, not a2 because a2 is associated with c2 too, not a3 because a3 wasnt associated with c1.

The problem I'm facing is an order problem.

I can't delete B first, because if I delete B, I lose the informations about the association between A and c1.
I can't delete c or a first because of the foreign keys. I cant change the database to add a cascade.
I can't delete all the "no relationship a" because I want to keep a3

I'm feeling there is an obvious solution but I cant fing it...

Use an OUTPUT clause to capture the relationships deleted from B and store them in a @ table variable, Then use the @ table variable to delete those records from A and C. — SOS
– SOS, Commented Feb 23, 2022 at 0:48

Charlieface · Accepted Answer · 2022-02-23 01:01:06Z

1

As mentioned by @SOS in the comments, you can use the OUTPUT clause when deleting from B to capture any relevant IDs for A. You then delete any associated A records, but exclude any that are still associated to another B record:

DECLARE @tmp TABLE (ID_A);

DELETE b
FROM B b
OUTPUT deleted.ID_A
  INTO @tmp (ID_A)
WHERE b.ID_C = 'c1';

DELETE a
FROM A a
WHERE a.ID_A IN (
    SELECT t.ID_A
    FROM @tmp t
    EXCEPT
    SELECT b.ID_A
    FROM B b
);

DELETE c
FROM C c
WHERE c.ID_C = 'c1';

answered Feb 23, 2022 at 1:01

community wiki

Charlieface

Sign up to request clarification or add additional context in comments.

1 Comment

Showb Over a year ago

Thanks a lot, it solves perfectly my problem

Collectives™ on Stack Overflow

SQL Server : Delete rows based on foreign key

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related