Delete rows from multiple tables using a single query (SQL Express 2005) with a WHERE condition
Solution 1
You cannot DELETE
from multiple tables with a single expression in SQL 2005
- or any other standard SQL for that matter. Access
is the exception here.
The best method to get this effect is to specify FOREIGN KEYS
between the table with an ON
DELETE
trigger
.
Solution 2
Why you don't use a DELETE CASCADE FK
?
Solution 3
This cannot be done in one statement. You will have to use 2 statements
DELETE FROM TB1 WHERE PersonID = '2';
DELETE FROM TB2 WHERE PersonID = '2';
Solution 4
As i know, you can't do it in a sentence.
But you can build an stored procedure that do the deletes you want in whatever table in a transaction, what is almost the same.
Solution 5
I don't think you can delete from multiple tables at once (though I'm not certain).
It sounds to me, however, that you would be best to achieve this effect with a relationship that cascades deletes. If you did this you would be able to delete the record from one table and the records in the other would be automatically deleted.
As an example, say the two tables represent a customer, and the customer's orders. If you setup the relationship to cascade deletes, you could simply delete record in the customer table, and the orders would get deleted automatically.
See the MSDN doc on cascading referential integrity constraints.
Jobi
Updated on February 10, 2020Comments
-
Jobi about 4 years
This is the query I'm using:
DELETE TB1.*, TB2.* FROM TB1 INNER JOIN TB2 ON TB1.PersonID = TB2.PersonID WHERE (TB1.PersonID)='2'
It's working fine in MS Access but getting error (Incorrect syntax near ','.) in SQL Server Express 2005.
How to solve it? Please help.