Deleting records from multiple tables at a time with one query in sqlserver2005
I want to delete records from child tables as well as parent table in one query. please refer to the query 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 a throwing mistake. can anyone help me find the correct query
a source to share
Unless you are using any cascading delete, a single delete statement will delete rows from one table.
In your example, if the syntax is correct, you only remove rows from responseheader
, the rest of the tables are only used to determine which rows to remove from responseheader
.
To be blunt, you really don't want to use cascading delete, so you have to run multiple delete statements, one in each table.
a source to share
You can create a stored procedure and call this stored procedure to delete. Or, you can use sp_executesql to send batch requests in one go like below:
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'
a source to share
As mentioned, Lasse cascading delete is the only way to delete from multiple tables with a single query. To do this, you must set up a foreign key and delete a record in the master table. There, rows in the child table will be deleted. But it's better not to use it. Better to use multiple delete operators. You can also use a transaction by setting auto commit to false. Then remove the lines and manually copy or rollback as needed.
a source to share