0

I'm writing a query which finds duplicate results in one table Table A based on a foreign key from Table X. Table A's Primary key is then a foreign key to 3 other tables, Table B + Table C + Table D

Is there a way I can write a single script to remove the first (top) row of 3 tables (Tables B C D)which use the FK and then Table A (via the FK of Table X).

Hope this makes sense, it's very hard to explain!!

Table A

Primary Key                                   Foreign Key                   IsActive

a64c3cae-8474-4c4e-8108-14898b8b5319    3b7c82d9-bbbe-4504-9dfd-e01614d12ff6    1

Table B 

Primary Key                                       Foreign Key               IsActive
998852b4-6d2a-47e0-b9c3-497548b8b5c6    a64c3cae-8474-4c4e-8108-14898b8b5319    1

Table C 

Primary Key                                 Foreign Key                     IsActive
0966f8e6-4c29-4e5a-8b59-5ac413557392    a64c3cae-8474-4c4e-8108-14898b8b5319    1

Table D 

Primary Key                                     Foreign Key                 IsActive
f8c30ff5-498f-4e34-90c5-814bc5cf46ef    a64c3cae-8474-4c4e-8108-14898b8b5319    1

Table X 

Primary Key                             Name    InTables
3b7c82d9-bbbe-4504-9dfd-e01614d12ff6    Bob         4

Thanks, Sam

SCramphorn
  • 447
  • 4
  • 23

1 Answers1

0

You can execute it in one query like this :

DELETE A, B, C, D
FROM TableA A
LEFT JOIN TableX X ON X.PK = A.FK
INNER JOIN TableB B ON B.FK = A.PK 
INNER JOIN TableC C ON C.FK = A.PK
INNER JOIN TableD D ON D.FK = A.PK;

However, this is not a good practice, as you can use foreign key CONSTRAINT ON DELETE CASCADE to be more efficient. What it does it's just link the foreign key to the linked primary key, if you delete that a linked primary key, any linked foreign key will be deleted as well.

Review this post to know more

iSR5
  • 3,274
  • 2
  • 14
  • 13