List:General Discussion« Previous MessageNext Message »
From:Rob Desbois Date:June 30 2006 3:44pm
Subject:'on duplicate key update' and 'last_insert_id'
View as plain text  
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
id.

I don't want to perform an INSERT IGNORE as this ignores far more errors than just
duplicate keys.
I'd rather not use REPLACE as if the unique key matches then the rest of the row
definitely matches.

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
sense?
Thanks ;-D
Rob


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________
Thread
'on duplicate key update' and 'last_insert_id'Rob Desbois30 Jun
  • Re: 'on duplicate key update' and 'last_insert_id'David Hillman30 Jun