> But using the concept of an "insert or update" style statement, the

primary key would need to be known

> before the call was made. If a key was unknown, I could just check

whether the value was null and run

> an update if it wasn't.

In our case, the primary key was generated, but three other fields also
were unique (i.e. a candidate key).
Thus the where statement of the update was composed by those three
values.

> I do not consider "skipped" generator values to be a problem

Make sure your data set isn't mostly updates. We load multiple drug
catalogs every week, so we would lose upwards of 150,000 keys a week if
we did insert/update instead of update/insert.

> any performance pros / cons over method 3?

The update must do a select underneath in order to find the record to
update, so #4 would double the number of selects.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: Wednesday, June 22, 2005 8:35 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: best way to insert or update

Hello Rick,

--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@r...>
wrote:

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

We do not use ours trigger to generate keys. But using the concept of an
"insert or update" style statement, the primary key would need to be
known before the call was made. If a key was unknown, I could just check
whether the value was null and run an update if it wasn't.

In any case, I do not consider "skipped" generator values to be a
problem. Primary keys are not required to be sequential, simply unique.

I am leaning towards method 3 anyway because I do not know how much
"work" I have made FB do during that failed insert.

Here is another possible method

Method4:


select 1
from tableA
where id = :ID
into :RecordExists;

if (:RecordExists = 1) then
begin
update TableA ...
end
else
begin
insert into TableA
end;

I do not know whether this has any performance pros / cons over method
3 though?


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