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.


When logging to a compact table that stores data in an aggregate form, I
used something like that:

BEGIN;
UPDATE ... ;

if (!affected_rows)
{
     INSERT ... ;

     if (error)
     {
         ROLLBACK;
         UPDATE ... ;
     }
}

COMMIT;

I added the error check with a second UPDATE try after INSERT to
increase accuracy. In fact, INSERTs were sometimes failing because of
concurrency, and this was the only viable solution I found to avoid
losing data.


Best regards
--
Matteo Beccati
http://phpadsnew.com/
http://phppgads.com/