deleting records from multiple tables at a time with a single query in sqlserver2005

Question

Hi

I wanna delete records from child tables as well as parent table with in a single query. please find the query given below. here response header is the primary table and responseid is the primary key.

DELETE FROM responseheader
    FROM responseheader
    INNER JOIN responsepromotion ON responseheader.responseid = responsepromotion.ResponseID
    INNER JOIN responseext ON responsepromotion.ResponseID=responseext.ResponseID 
    WHERE responseheader.responseid In ('67D8B9E8-BAD2-42E6-BAEA-000025D56253')

but its throwing error . can any one help me to find out the correct query

Answer 1

Unless you use some kind of cascading delete, a single delete statement will delete rows from a single table.

In your example, if the syntax is correct, you will be deleting rows from responseheader only, the rest of the tables are only used to determine which rows to delete from responseheader.

To be blunt, you really don't want to use a cascading delete, so you should execute multiple delete statements, one in each table.

Answer 2

You can either create a stored procedure and call that stored procedure to do the deletion. Or you can use sp_executesql to send batch queries in one go, as follows:

sp_executesql 

    'DELETE FROM responsepromotion
        FROM responsepromotion
        INNER JOIN responseheader ON responseheader.responseid = responsepromotion.ResponseID
        WHERE responseheader.responseid = @guid;

    DELETE FROM responseext
        FROM responseext
        INNER JOIN responseheader ON responsepromotion.ResponseID=responseext.ResponseID
        WHERE responseheader.responseid = @guid;

    DELETE FROM responseheader
        WHERE responseid = @guid;',

    @guid = '67D8B9E8-BAD2-42E6-BAEA-000025D56253'

Answer 3

As mentioned by Lasse cascade delete is the only option to delete from multiple tables with a single query. For that you should setup foreign key and delete the entry in the master table. There by the rows in the child table will get deleted. But its better not used. It will be better to use multiple delete statements. You can also use transaction by setting auto commit to false. Then delete the rows and manually commit or rollback as required.