SQL Deleting rows from multiple tables

Question

I've been trying to delete the the same highest value from multiple tables, however I get a syntax error on t1 and t3. Any ideas how to fix it? Btw I'm using MS SQL (TSQL)

DELETE FROM ProductsOrders t1, Orders t2
    JOIN ( SELECT MAX(OrderID) FROM Orders) t3
WHERE t1.OrderID = t3.OrderID
AND t2.OrderID = t3.OrderID

Even my edits have been gone. I guess it's the webpage either went on backup or something else... — TesteroniPeperoni, May 25 '16 at 20:13
Look at this answer: http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query Personally I find it a much better design to have a foreign key with a cascade on delete. — SunKnight0, May 25 '16 at 20:16
your answer was correct! And thank you, though in ms sql you cant delete from multiple tables. You have to use DELETE for every table — TesteroniPeperoni, May 25 '16 at 20:17

score 0 · Answer 1 · answered May 26 '16 at 03:08

You can't delete from multiple tables with one statement in T-SQL statement, with the exception of cascading deletes.

Here is a simple solution:

DECLARE @MaxOrderID INT

BEGIN TRAN

SELECT @MaxOrderID = Max(OrderID) FROM Orders

DELETE FROM ProductOrders WHERE OrderID = @MaxOrderID
DELETE FROM Orders WHERE OrderID = @MaxOrderID

COMMIT TRAN

SQL Deleting rows from multiple tables

1 Answers1