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

Question

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

Always post as much information as you have. For instance, "throwing error"? Please post the error message you get. If the error message "Table responseheader does not exist", there is not much we can do to help you, so we really need to see what the database engine complains about. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Apr 23, 2010 at 6:12

Lasse V. Karlsen · Accepted Answer · 2010-04-23 06:14:15Z

3

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.

answered Apr 23, 2010 at 6:14

Lasse V. Karlsen

394k107 gold badges651 silver badges845 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amry · Accepted Answer · 2010-04-23 06:40:06Z

0

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'

answered Apr 23, 2010 at 6:40

Amry

4,9912 gold badges25 silver badges24 bronze badges

Comments

zapping · Accepted Answer · 2010-04-23 06:43:07Z

0

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.

answered Apr 23, 2010 at 6:43

zapping

4,1266 gold badges40 silver badges56 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related