- Even if you do not find the record (always use EXISTS instead of COUNT)
it does not mean that it does not exist:
- it can already be inserted by other user but not committed, or
- it can be inserted and committed, but your snapshot transaction does not see it

- Even if the record really does not exists, it does not mean somebody faster
can't insert it after your existence test and before insert.

Relying on exceptions is safer way.

Ivan Prenosil
[ I am looking for a job: InterBase - Firebird - Delphi - C - fulltext db - and more ... ]
Ivan.Prenosil@...
http://www.volny.cz/iprenosil/interbase

----- Original Message -----
From: "Nigel Weeks" <nigel@...>
> Use a stored procedure, and don't rely on Primary Key execeptions to do your
> checking...
>
> ...
> /* 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
...

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