|Subject||Re: [firebird-support] check for exist with assignment|
> Option 2:Do not forget that this requires NEWID to be set to Null before calling select !
> select ID from CITIES where CITY = :NEWCITY into :NEWID;
> if ( NEWID is not null ) then suspend;
> /* Do insert operation since nothing found */
You can also test ROW_COUNT variable.
> Option 4 (since this is a two-column table):The drawback is that this updates the row even if the same values already exist in the 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 */