How to delete records from Child table and Parent table in a single query?

Question

Hi all,

I have a master table and a details table. On delete cascade is not specified.

I want delete child record as well as master record in a single query.

Suppose I want to delete EmployeeDetails and Employee record where EmpID=20 using a single query.

Is it possible?

Please help.

Answer 1

There is no construct in SQL that allows you to delete from two tables in a singe command. You can do that in a single "batch" or in a transaction (which will be preferable).

Answer 2

you cannot do it in a single query unless you have cascade delete turned on or you have a trigger on the PK table that will delete the FK table rows for that relationship

Answer 3

you could add a trigger on the child table to delete any other children and then the parent. This is not "technically" a single statement, but your application only needs to issue a single DELETE and it is all done for you.