Use a stored procedure, and don't rely on Primary Key execeptions to do your
checking...


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
begin
/* We have something */
update table set val1 = :val1, val2 = :val2
where primary key field = :primary key field;
end
else
begin
/* Nothing was found */
insert into table (val1, val2)
values (:val1, :val2);
end
/* Send out the int_success(with the 0 or 1 for insert or update)*/
SUSPEND;
end !!
set term ; !!


-----Original Message-----
From: gorepj [mailto:peter@...]
Sent: Friday, 4 July 2003 14:01
To: firebird-support@yahoogroups.com
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
//Try to append record
INSERT INTO MyTable (Myfield ...) VALUES (MyValue ...)
EXCEPT
//Primary key violated therefore update that record instead
UPDATE MyTable SET MyField = MyValue WHERE KeyField = SearchKeyValue
END;

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:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/