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