If you have a trigger and generator doing auto-key generation, method
two will use up a key with every failed insert. Because of this we use
method three.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Monday, June 20, 2005 7:34 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] best way to insert or update

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




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links