2

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

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
sudhavamsikiran
  • 21
  • 1
  • 2
  • 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 Apr 23 '10 at 06:12

3 Answers3

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.

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
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'
Amry
  • 4,791
  • 2
  • 23
  • 24
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.

zapping
  • 4,118
  • 6
  • 38
  • 56