Hello Group,

I am looking for the best way to insert a record into a table (if it
doesn't exist) or update the record in the table (if it does exist)
using a stored procedure.

I have thought about a few ways of achieving this

Method 1:

delete from tableA where ....
insert into tableA ...

This is not going to work because there are foreign key constraints
that will cascade the delete and this is not desirable

Method 2:

begin
insert into tableA ....
when -803 do
begin
-- primary key violation
update tableA set .....
end
end

This looks like it should work OK

Method 3:

update tableA set ....
if (ROW_COUNT = 0) then
begin
insert into tableA .....
end

This looks like it should work OK as well in FB1.5.

There will be a pretty good mix between records that need to be
inserted and records that need to be updated, not quite 50-50, but
probably 60-40 (update-insert).

Which method would perform best under this sort of scenario and why?

Adam