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

Mark Harrison wrote:
> I've been doing something like
>
> delete from foo where name = 'xx';
> insert into foo values('xx',1,2,...);
>
> but I've been wondering if there's a more idiomatic or canonical
> way to do this.

The delete+insert isn't quite the same as an update since you might have
foreign keys referring to foo with "ON DELETE CASCADE" - oops, just lost
all your dependant rows. Other people have warned about race conditions
with insert/test/update.

An "update or insert" would be useful sometimes, but it's not always
necessary. Indeed, if I find I don't know whether I'm adding or updating
something I take a long hard look at my design - it ususally means I've
not thought clearly about something.

For a "running total" table it can make more sense to have an entry with
a total of 0 created automatically via a trigger. Likewise with some
other summary tables.

Can you give an actual example of where you need this?

--
Richard Huxton
Archonet Ltd