|Subject||Re: UPDATE/INSERT strategy|
> Is it better to UPDATE, detect 0 rows affected, then INSERTIt is better if Firebird supported the 'merge' syntax (which is coming
> or INSERT, detect duplicate key, then UPDATE?
to some extent with 'replace').
> The INSERT will succeed once, while the UPDATE will succeed manyYou can also catch the exception and send an update in response. We
> times, so it seems the first choice is better.
have used stored procedures in the past to do it in one database trip
using the update then insert method.
Be aware however that transaction isolation can cause both to fail.
For example, if a simultaneous uncommitted transaction has inserted
the same record, your insert statement will receive the constraint
violation, but the update will not see that record. You need to have
built into your design some way of dealing with it.