0

There's an issue in our software which sometimes requires a row to be deleted from a table, but this has links to other tables that's also need to be deleted, so I wanted to write a script to be able to delete all of these at one.

The issue I'm having is that I want these all in one script, but I only know the PC Name, which is referenced in only 1 table, so I cant to use another value in that row that's the same throughout.

| CabID | CabName |
| 7     | PC25    |

So I know the 'CabName', and want to enter this in the script but then replace '#' with CabID'

First part I have is

SELECT * 
FROM Cabinets
WHERE CabName= 'PC25'

Then

DELETE FROM Room_Cab_Assoc
WHERE CabID= '#',

DELETE FROM PatientCabAssignment
WHERE CabID= '#',

DELETE FROM BinAssignments
WHERE CabID= '#',

DELETE FROM Drawers
WHERE CabID= '#',

DELETE FROM FeatureLogins
WHERE CabID= '#',

DELETE FROM Cabinets 
WHERE CabID= '#';

Or is there a better way of doing this?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Nick Baynes
  • 1
  • This is a similar question: https://stackoverflow.com/questions/4839905/mysql-delete-from-multiple-tables-with-one-query – Yash Laddha Jul 13 '22 at 15:55
  • The only alternative would be to have cascading in your database. For simply designs you can enable the `CASCADE` feature on the foreign key, but otherwise you would likely need to use well written set based DML Triggers. If all of your interactions are via procedures though, then doing this logic in the procedure is likely the better place, and just have the keys maintain the integrity. – Thom A Jul 13 '22 at 16:01
  • 1
    Note that the "delete from multiple tables" syntax provided by the answer in the question linked by @YashLaddha is not available in SQL Server. [Here are your options](https://stackoverflow.com/q/783726/7165279) for SQL Server. – allmhuran Jul 13 '22 at 16:04

0 Answers0