Cleanup orphaned records I have two tables in MySQL: orders and line items.

When orders are deleted, the line items are orphaned. Thus, I need a query that will delete the line items that were associated with the order before the order was deleted.

Something like:

 DELETE FROM line_items WHERE line_items_id NOT EXIST IN TABLE orders COLUMN orders_id


In other words, of the order id referenced in the line item row does not exist, then delete the line item in question.

(I know... it would have been better if the person who designed this had used foreign keys and cascade... but they didn't).
DELETE FROM Line_Items 
WHERE Line_Items.Order_Number -- this should be an ON DELETE CASCADE FK.
NOT IN 
(
  SELECT id 
  FROM Orders
); -- not tested, don't have running system, but this or similar should work.


Note: Don't call a table "Order" - makes debugging difficult and system non-portable. Personally, I'm against the use of plural names for tables - I usually call it System_Order (or Shop_Order) or similar.