Subject Re: UPDATE/INSERT strategy
Author Adam
> Is it better to UPDATE, detect 0 rows affected, then INSERT
> or INSERT, detect duplicate key, then UPDATE?

It is better if Firebird supported the 'merge' syntax (which is coming
to some extent with 'replace').

> The INSERT will succeed once, while the UPDATE will succeed many
> times, so it seems the first choice is better.

You can also catch the exception and send an update in response. We
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.