MySql: delete from multiple tables using left join

Question

I have this tables:

Orders: OrderId, UserId
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?

Thats weird since the WHERE in the LEFT JOIN will make it an INNER JOIN anyway.Change you WHERE to AND in your second query to get all orders. — Mihai
– Mihai, Commented Jan 17, 2015 at 10:50
@Mihail , no. this condition "ON o.OrderId = oi.OrderId AND o.UserId = 11" selects orders with items only. — Maxim Tkachenko
– Maxim Tkachenko, Commented Jan 17, 2015 at 11:32
Post it as your own answer and accept it to solve the question — Mihai
– Mihai, Commented Jan 17, 2015 at 15:30

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 18, 2015 at 19:57

Maxim Tkachenko

5,85610 gold badges43 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql: delete from multiple tables using left join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related