JamiePatt JamiePatt - 5 months ago 12
SQL Question

Deleting from one table where a field on a foreign key is less than value in MySQL

I have a database table called 'order' which references another table named 'delivery' through a foreign key. I need to delete deliveries modified before a certain date but I can't do so until I delete the orders that reference them.

So I need to delete orders where deliveries were modified before a certain date. I wrote the following query in MySQL:

DELETE FROM `order`,`delivery` WHERE order.delivery_id = delivery.id AND delivery.modifiedOn < '2016-2-28 23:59:59'


But I'm getting a SQL error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near: 'WHERE order.delivery_id = delivery.id AND delivery.modifiedOn < '2016-'

I've not done this kind of query before, I suspect I need to use a JOIN somewhere but not sure where - any advice would be appreciated.

Answer

This is the syntax you are looking for:

DELETE o, d
    FROM `order` o JOIN
         `delivery` d
         ON o.delivery_id = d.id 
    WHERE d.modifiedOn < '2016-02-29';

Notes:

  • Don't name a table order. It is a reserved word in MySQL and a keyword in SQL. How about Orders?
  • Learn to use proper JOIN syntax, even in non-SELECT queries.
  • Table aliases make a query easier to write and to read.
  • There is no need to include the HH:MM:SS for the time comparison.
  • Do you realize that February 2016 actually had 29 days?
  • Finally, cascading delete foreign key references would simplify the processing.