Subject RE: [firebird-support] Better way to update table?
Author Nigel Weeks
Use a stored procedure, and don't rely on Primary Key execeptions to do your

set term !! ;
create procedure sp_insert(
val1 type,
val2 type,
) returns (
int_success integer
) as begin
/* Find if it's there (Use primary keys if possible)*/
select count(*) from table where val1 = :val1, val2 = :val2...
into :int_success;

/* Test is we've got it */
if(int_success >0)THEN
/* We have something */
update table set val1 = :val1, val2 = :val2
where primary key field = :primary key field;
/* Nothing was found */
insert into table (val1, val2)
values (:val1, :val2);
/* Send out the int_success(with the 0 or 1 for insert or update)*/
end !!
set term ; !!

-----Original Message-----
From: gorepj [mailto:peter@...]
Sent: Friday, 4 July 2003 14:01
Subject: [firebird-support] Better way to update table?

I have an Import routine that updates a table. If a record already
exists then it is updated otherwise it is appended. I impelement this
by having a primary key constraint and program SQL statements in
Delphi accordingly.
//Try to append record
INSERT INTO MyTable (Myfield ...) VALUES (MyValue ...)
//Primary key violated therefore update that record instead
UPDATE MyTable SET MyField = MyValue WHERE KeyField = SearchKeyValue

This works beautifully but if I don't commit every statement I lose
cached updates / inserts when the exception occurs.

Is there a better way?
Peter Gore

To unsubscribe from this group, send an email to:

Your use of Yahoo! Groups is subject to