I've seen a couple of questions here in the past about how to update an existing row or insert a row if it doesn't exist without race conditions that could cause you to have to retry a transaction. I didn't find any answers to this question in the archives however, so I thought I'd post my solution here for the edification of others.
INSERT INTO my_table (key, value) SELECT 1, 'a value' WHERE NOT EXISTS (SELECT key FROM my_table WHERE key = 1); UPDATE my_table SET value = 'a value' WHERE key = 1; This, as far as I can tell, will never fail, though it might invoke triggers that would not otherwise be invoked if only the single necessary statement (the INSERT or the UPDATE) were executed. I'd imagine in most cases that this would not be a big problem. IMHO, this is a little bit better than the MySQL REPLACE command, since it doesn't cause deletion of an entire row and reinsertion, which can play hell with things like my change logging triggers. (It would record all the deleted values in the change log table and then an insert, rather than recording just the changed values.) If you have any comments you want me to see, please be sure that I'm in the cc list on the message, as I'm not subscribed to this list. cjs -- Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org Don't you know, in this new Dark Age, we're all light. --XTC ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])