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

Hi,

I prefer to update and if the number of updated rows equals 0 do an insert. So
in case of update I need only one roundtrip. If insert is far more common in
this case it might be better try insert and catch the error. But I try to
avoid running on an error intentionally.

First delete and then insert works but needs 2 SQL-statements in every case.
And the database need to update indexes at least once. There might be also
problems with cascaded deletes.


Tommi

Am Donnerstag, 5. August 2004 01:25 schrieb Mark Harrison:
> So I have some data that I want to put into a table.  If the
> row already exists (as defined by the primary key), I would
> like to update the row.  Otherwise, I would like to insert
> the row.
>
> 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.
>
> Many TIA,
> Mark