Deleting rows from multiple rows in SQL 2005

[laxatcg1]

Hi all,

"Deleting rows from multiple tables in SQL 2005"

I need to delete all the rows in the multiple rows where it's the same UserId. Please look at my Delete command below:

===============

DELETE FROM tableA
FROM tableA INNER JOIN
tableB ON tableA.UserId = tableB.UserId INNER JOIN
tableC ON tableA.UserId = tableC.UserId INNER JOIN
tableD ON tableA.UserId = tableD.UserId
WHERE (tableA.UserId = @UserId) AND (tableB.UserId = @UserId) AND (tableC.UserId = @UserId) AND (tableD.UserId = @UserId)

===============

What is happening from the above query is only the rows from tableA is getting deleted.

Please help me on this.

Thank you!

[dglienna]

If there are keys, you can set cascading delete to be true

[Cimperiali]

or else you will have to write a Stored procedure that executes multiple deletions, deleting first from children tables and then from parents

delete from tableChidlA where

delete from tableChildB where

delete from tableFather where

but in this case to be sure you preserve integrity, you should also use transaction