Re: most idiomatic way to "update or insert"?

I don't think that works - there's a race condition if you do not do any
locking.

Why:
Before a transaction that inserts rows is committed, other transactions are
not aware of the inserted rows, so the select returns no rows.

So:
You can either create a unique index and catch insert duplicate failures.

Or:
lock the relevant tables, then do the select ... update/insert  or insert
... select , or whatever it is you want to do.

Or:
both.

Test it out yourself.

At 07:51 AM 8/5/2004, Peter Darley wrote:
>Mark,
>         It's not canonical by any means, but what I do is:
>
>update foo set thing='stuff' where name = 'xx' and thing<>'stuff';
>insert into foo (name, thing) (select 'xx' as name, 'stuff' as thing where
>not exists (select 1 from foo where name='xx'));
>
>         I believe if you put these on the same line it will be a single
>transaction.  It has the benefit of not updating the row if there aren't
>real changes.  It's plenty quick too, if name is indexed.
>
>Thanks,
>Peter Darley