I wanna delete records from child tables as well as parent table with in a single query. please find the query开发者_如何学C 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
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.
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'
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.
精彩评论