Delete rows from 2 tables in single query

Question

Hi,

Is there any way to delete rows from more than one tables in one go ?

I have 2 tables
Tbl1 (id,c1)
tbl2 (id,c1)

I want to delete rows from both tables in one go.

I tried below but it has syntax error. Pls suggest

DELETE tbl1 , tbl2 FROM tbl1 A INNER JOIN tbl2 B ON A.id= B.id
and A.id <50

Answer 1

delete rows from more than one tables in one go ?

No, it's not possible, in a DELETE statement you can address only one table, not more.
See https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-ver16

Answer 2

Hi @Santosh
As Olaf answered, you cannot Delete From multiple tables with a single expression.
Maybe you could have a try on specify FOREIGN KEYS between the two tables with an on delete cascade.
Then, when you delete a record from the master table all other details table record based on the deleting rows primary key value, will be deleted automatically.

Best regards,
LiHong