Delete mysql query

My current issue is as follows. The below select script works开发者_Python百科 perfectly.

SELECT * 
FROM orderheader, orderbody, orderbodylocation
WHERE orderheader.DateCreated > '2011-02-01 00:00:00' 
AND orderheader.OrderID = orderbody.OrderID 
AND orderbody.OrderBodyID = orderbodylocation.OrderBodyID 
AND orderbody.OrderID = orderbodylocation.OrderID 
AND orderheader.Status <= 1

but the below delete query which is a duplication of the where clauses fails around the dateCreated clause.

DELETE 
FROM orderheader, orderbody, orderbodylocation
WHERE orderheader.DateCreated > '2011-02-01 00:00:00' 
AND orderheader.OrderID = orderbody.OrderID 
AND orderbody.OrderBodyID = orderbodylocation.OrderBodyID 
AND orderbody.OrderID = orderbodylocation.OrderID 
AND orderheader.Status <= 1

the DateCreated field within orderheader table is a DATETIME type.

I'm trying to delete every order newer then 1-2-2011 (d-m-y) along with the orderheaders associated table data in orderbody and orderbodylocation tables.

I've run out of ideas and I haven't had much experience with mysql DateTime datatype fields. If anyone can see my error it would be greatly appreciated if you could let me know what I've done wrong.

If the manual page to which zerkms have courteously posted a link applies to your version of MySQL, then you simply need to add (once again) the table(s) you want to delete from to the DELETE instruction. You need to put them between DELETE and FROM, like this:

DELETE table1, table2, ... /* probably some or all tables in the FROM list */
FROM orderheader, orderbody, orderbodylocation
...

Posting the error message would be helpful!

I don't know mysql but if it's like other SQLs you cannot reference multiple tables in the FROM clause of a DELETE command.

If you have foreign keys with the cascade delete property then just do a delete for the header rows and the rest will be automatically deleted.

If you don't have cascade delete set up for the related tables then you will need to do 3 delete statements, deleting data from the bottom up. For the lower-level tables you will need to use a sub-select to match against the header table. Here's an example for the order body:

DELETE FROM orderbody where orderbody.OrderID in (select orderHeader.OrderID FROM orderheader WHERE orderheader.DateCreated > '2011-02-01 00:00:00' AND orderheader.Status <= 1)

HTH

Leo