Is it possible to delete master AND detail records in one SQL statement?

Question

Cascading deletes aren't setup on this particular database. Just wondering if there's a way to do it in one swipe that's more efficient than running two separate statements.

Answer 1

No.

You could do two statements on a single command:

DELETE FROM ChildTable; DELETE FROM ParentTable;

But in effect that is two statements.

or

You could put a trigger on the ChildTable that deletes parent records.

Neither meets your criteria.

Answer 2

Should be in a transaction - it may be less efficient but much better for data integrity

BEGIN TRAN
DELETE FROM ChildTable WHERE safetycatch=0
DELETE FROM ParentTable WHERE safetycatch=0
COMMIT TRAN

Edit - if you really want to use only one statement then you could create a stored procedure e.g.

CREATE PROCEDURE DeleteBoth AS
    BEGIN TRAN
    DELETE FROM ChildTable WHERE safetycatch=0
    DELETE FROM ParentTable WHERE safetycatch=0
    --ERROR HANDLING IF STUFF GOES WRONG THEN ROLLBACK TRAN
    COMMIT TRAN
GO

then you can run it in one line from then on with

EXEC DeleteBoth

With a cached execution plan, I imagine the performance difference would be negligible compared to a cascading delete as that's pretty much what's going on behind the scenes anyway.

Answer 3

As Nissan Fan mentions, triggers would do it. As wefwfwefwe suggests, use a transaction if it has to be all-or-nothing.

Depending on how desperate you are, you can combine both in an over-complex way to do it with a single statement issued to the database:

1. Build a view on a join of the tables.
2. Add an INSTEAD OF trigger on it, coded to drop the appropriate rows from both tables. Not really recommended, but you could do it.

Answer 4

Yes, but only if you define the foreign key relationship and use the ON DELETE CASCADE option.

Everything else will require two statements, even if the statements happen to be in the same transaction.

Answer 5

Normally there is a good reason for not setting up cascading deletes (often having to do with blocking and locks). Since they are not set up you need to write separate delete statements for all child and parent tables yourself. I would suggest the best way is in a stored proc using transactions as you do not want to do partial deletes if you care about the integrity of your data. Make sure to have error trapping and rollbacks.