MySQL DELETE and many to many relationship

Question

Given these schema

orders(order_number,created,status) products(product_id,product_name) orders_products(order_number,product_id,quantity)

If I would like to delete orders older than N days, I would have to delete also the records related to those orders in orders_products table.

How could I do this in a single query, without having to retrieve every single order_number, store them in the business logic and finally to run additional queries to clean the mapping table?

I don't have any FK

Possible duplicate of [MySQL foreign key constraints, cascade delete](https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete) — mentallurg, Jun 24 '18 at 09:27
use `ON DELETE CASCADE` on pivot table in your DB structure, as mentioned by @mentallurg !!! — TalESid, Jun 24 '18 at 09:51

score -1 · Answer 1 · edited Jun 24 '18 at 19:30

-1

This seems to work:

DELETE orders_products, orders 
FROM orders_products 
INNER JOIN orders 
ON orders_products.order_number = orders.order_number 
WHERE orders.created  < NOW() - INTERVAL 30 DAY

edited Jun 24 '18 at 19:30

Rishikesh Chandra

661
9
25

answered Jun 24 '18 at 10:29

Crasher

2,211
2
18
17

Use cascade instead. – mentallurg Jun 24 '18 at 12:28

MySQL DELETE and many to many relationship

1 Answers1