I've used ON DUPLICATE KEY UPDATE in MySQL and it pretty much does exactly what it says on the tin. Seems to work well.
A more database-neutral alternative is to select the key from the table, and if it doesn't exist then insert, otherwide update.
You'll need to wrap this in a transaction to avoid the race condition kyle mentions above (unless you are the only process working on the db). In MySQL this means you either need to get a table lock, or switch your table type to InnoDB and do a row-level transaction as described on this page: InnoDB locking reads.
By the way, ON DUPLICATE KEY UPDATE will lock the auto-increment table if you have an auto-increment field in your table, so this situation is one that can cause locking problems whichever solution you choose, if you have a lot of concurrent inserts/updates.
(update: will only lock the auto-increment table if an insert actually takes place, I think - still, a potential problem)
As I see it, the problem with the original solution (use insert, check for error, use insert if update didn't work) is, what if the error wasn't a duplicate key error? To do this properly, I think you'd need to check the error type.
That way you could try again with an insert if the error was a duplicate key error, or if it was a different error then report the error and exit as usual. You'd need to ensure that RaiseError was switched off on your DBI handle.
HTH!
Best wishes, andye
In reply to Re^2: Of Mysql, DBI and PK best practices
by andye
in thread Of Mysql, DBI and PK best practices
by jimbus
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |