Write a stored procedure that takes the key value and the new value as a
parameter.

create procedure myproc( akey integer, avalue varchar( 100 ) )
as
begin

begin

insert into atable( blah ) values ( blah );

when sqlcode -803 do

update atable
set acol = :avalue
where akecol = :akey;

end
end

Not sure the syntax and code is quite right. Just attempt an insert and if
there is a duplicate key violation proceed to do the update. it doesn't get
any faster than doing it this way.

HTH,
Jason Wharton
CPS - Mesa AZ
http://www.ibobjects.com

----- Original Message -----
From: "Johan Kotze" <jkkotze@...>
To: <IBObjects@yahoogroups.com>
Sent: Thursday, July 19, 2001 7:53 AM
Subject: [IBO] Optimize updating of large database


> I have a database of 1 million+ entries and I am looking for the
> quickest way of retrieving a record (specified by a unique key
> value), change a field in the record (not the key field) and
> applying the change to the database. To complicate matters, if the
> key does not exist, a new record needs to be added to the database.
> I am currently using a TIB_Query component and using TIB_Query.Lookup
> to find the record, put the record in edit mode and executing post. I
> was thinking along the lines of a stored proc, but how do you
> accomplish this in a stored proc.
>
> Any ideas will be much appreciated.
>
> Johan Kotze
>
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>