[SOLVED] error when deleting from multiple tables and using datetime

[mechew]

I'm trying to dete rows older than a certain date, when I change the exact same query to a select * it works. But I get errors around the datetime where clause. I tried changing the datetime to unixtimestamp to see if it was an error with datetime sorting but still an error.

 

Here is my query:

DELETE FROM test t1, test2 t2 WHERE UNIX_TIMESTAMP(t1.date) < UNIX_TIMESTAMP() AND UNIX_TIMESTAMP(t1.date) > UNIX_TIMESTAMP('2007-04-04 00:00:00') AND t2.id = t1.id

 

And I've tried

DELETE FROM test t1, test2 t2 WHERE t1.date < '2007-11-03 00:00:00' AND t1.date > '2007-04-04 00:00:00' AND t2.id = t1.id

 

I've also tried DELETE *

 

Here is my structure for both tables

 

CREATE TABLE `test` (

`id` int(11) NOT NULL auto_increment,

`date` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

CREATE TABLE `test2` (

`id` int(11) NOT NULL default '0',

`blank` char(2) NOT NULL default 'te',

`more` char(3) NOT NULL default 'tes',

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[mechew]

I figured it out

 

 

DELETE FROM test, test2

USING test

LEFT JOIN test2 ON test2.id = test.id

WHERE UNIX_TIMESTAMP(t1.date) < UNIX_TIMESTAMP()

AND UNIX_TIMESTAMP(t1.date) > UNIX_TIMESTAMP('2007-04-04 00:00:00')