CODEWITHSUNDEEP
mysqlsqlleft-join
Maxim Tkachenko
Maxim Tkachenko

Reputation: 5798

MySql: delete from multiple tables using left join

I have this tables:

  1. Orders: OrderId, UserId
  2. OrderItems: OrderId, Price

There is foreign key on OrderItems.OrderId and it's possible to have order without order items. I want to delete all orders and order ietms for some user. This sql with inner join works fine:

DELETE o, oi 
FROM Orders o 
JOIN OrderItems oi ON o.OrderId = oi.OrderId 
WHERE o.UserId = 11

but it doesn't delete orders without order items (inner join). But sql query with left join

DELETE o, oi 
FROM Orders o 
LEFT JOIN OrderItems oi ON o.OrderId = oi.OrderId 
WHERE o.UserId = 11

throws error

cannot update or delete parent row.

What's wrong? is it possible to delete all orders (with items and without) for user in one query?

Upvotes: 0

Views: 1399

Answers (1)

Maxim Tkachenko
Maxim Tkachenko

Reputation: 5798

Here is explained that

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly

So I'll just use sequent deletion.

Upvotes: 1

Related Questions