June 9, 2014 at 11:07 am
Hi,
My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.
For this one I need to write 4 individual delete statements.
Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?
I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error
June 9, 2014 at 12:00 pm
ramana3327 (6/9/2014)
Hi,My requirement is before inserting new data, we need to delete the old data based on the input in 4 tables.
For this one I need to write 4 individual delete statements.
Is it possible to delete rows from multiple tables in single statement in SQL Server 2012 by using joins?
I am looking similar, I tried by keeping 4 table aliases in delete statement but it is throwing synatx error
No. You cannot delete rows from multiple tables in a single statement.
What you can (and I do not recommend) is joining the tables let's say with their ID or common column, put that set on a temporary table, and the run separate delete statements that will delete each set based on the IDs saved on the temporary table.
But again, I don't see any logical reason for doing that. You can mess up the integrity of your application.
If you need to maintain referential integrity between two tables let's say based on ID, you must use FK constraints.
June 9, 2014 at 1:27 pm
Agree - not possible.
I would use this construct (with each "check for error" being a rollback and terminate and message):
BEGIN TRAN
delete table1 where ...
check for error
delete table2 where ...
check for error
delete table3 where ...
check for error
delete table4 where ...
check for error
COMMIT TRAN
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply