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
______________________________________________________________________
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
