Choosing correct MySQL query
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.
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`)
精彩评论