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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
