--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:

>
> > 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.
>

I have a similar instance of the same problem and overcame it with
simply using an additional transaction, creating an UPDATE statement
on the fly when the INSERT fails with a duplicate entry, and running
the UPDATE via isc_dsql_execute_immediate().

Don't forget to roll back the original INSERT.
Kurt.