I have a table `event` with two keys:
`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
`location_id` MEDIUMINT(8) UNSIGNED NOT NULL,
`timestamp` DATETIME NOT NULL,
`type` ENUM('0','1','2','3','4','5','6','7','8','9') NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`location_id`,`timestamp`,`type`)
Sometimes a client will attempt to perform an insert into the table where the row has
already been inserted, i.e. the unique key already exists. In that case I want it to
appear as though it wasn't there before and has been inserted, returning the new value of
I don't want to perform an INSERT IGNORE as this ignores far more errors than just
I'd rather not use REPLACE as if the unique key matches then the rest of the row
That leaves me with ON DUPLICATE KEY UPDATE. It's not amazingly helpful as you have to
provide a column to update - however I can just say e.g.
ON DUPLICATE KEY UPDATE id=id
The problem with this is that if I then do "SELECT LAST_INSERT_ID();" then I don't get the
id of the 'updated' table, I get the *next* auto increment value.
Is the last bit a bug? Can I get what I want without using REPLACE? Does this post make
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email