Choosing correct MySQL query

Question

In my project I am using one MySQL table to store actual information for every id (unnecessary fields are omitted):

CREATE TABLE  mytable (
  `id` varchar(8) NOT NULL,
  `DateTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM

I need to update rows in this table and insert new DateTime only if this incoming DateTime is newer. But at the same time I do not know if the row with such id already exists in the table. Here is the pseudo-code of what I would like to achieve:

if id does not exist
    insert id and DateTime
else if newDateTime > DateTime
    update DateTime

• I tried to use replace, but as far as I know it is not possible to compare fields from within replace body.
• I tried to use update, but if row does not already exist - new information is not being inserted.

Is it possible to do something like this in one query? And if not - what could be the workaround?

PS: I do not think that I have permission to add stored procedures, so this should be a last resort.

Answer 1

I've tested this and it works. And I'm pretty proud of it.

INSERT INTO mytable (`id`,`DateTime`)
VALUES ('your_new_id','your_new_DateTime')
ON DUPLICATE KEY UPDATE `DateTime` = IF(`DateTime` < 'your_new_DateTime', 'your_new_DateTime', `DateTime`)