0

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.

JamiePatt
  • 43
  • 2
  • 10
  • Possible duplicate of [How to delete from multiple tables in MySQL?](http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) – Drew Jul 08 '16 at 00:50
  • So, just accept the duplicate callout at the top of your question if you want (for a multiple table delete if that is your situation, and I am not sure it is) – Drew Jul 08 '16 at 00:51
  • Jamie, the following is a simple example of cascading deletes [Here](http://stackoverflow.com/a/32298405) ... about half way down the answer is an example – Drew Jul 08 '16 at 01:19

1 Answers1

1

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.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786