Subject Re: [firebird-support] check for exist with assignment
Author Ivan Přenosil
> Option 2:
> select ID from CITIES where CITY = :NEWCITY into :NEWID;
> if ( NEWID is not null ) then suspend;
> /* Do insert operation since nothing found */

Do not forget that this requires NEWID to be set to Null before calling select !

You can also test ROW_COUNT variable.

> Option 4 (since this is a two-column table):
> insert or update into CITIES ( CITY ) values ( :NEWCITY ) matching ( CITY ) returning ID into :NEWID;
> /* This theoretically (in my theory, anyway) either practically does nothing if it exists
> (might result in activating update triggers and last modified timestamps), otherwise add the entry */

The drawback is that this updates the row even if the same values already exist in the table.