MySQL Forums
Forum List  »  Quality Assurance

error when deleting by datetime
Posted by: matt macnish
Date: November 03, 2007 09:49AM

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;

Options: ReplyQuote


Subject
Views
Written By
Posted
error when deleting by datetime
2534
November 03, 2007 09:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.