Solution to UPDATE or INSERT Problem

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 <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.NetBSD.org
Don't you know, in this new Dark Age, we're all light. --XTC