On 11/10/2013 12:10 AM, Mark Rotteveel wrote:

> The Firebird syntax doesn't allow an INTO in this context. Technically
> Firebird has two selects, one with the INTO clause and one without. The
> SELECT with INTO clause is only allowed in PSQL as a separate statement,
> or as part of a FOR SELECT ... DO.
>
> An EXISTS only allows a 'normal' select (that is: without an INTO clause).
>
> Mark

Fair enough. In that case, which of the following would be "better" ?
The context is a procedure call to update the city entry - if a ID is
passed to the procedure then it simply modifies the existing record.
The logic under discussion is for
insert - but with protection to avoid duplicates.

Option 1:
if ( exists( select ID from CITIES where CITY = :NEWCITY ) ) then begin
select ID from CITIES where CITY = :NEWCITY into :NEWID;
suspend;
end
/* Do insert operation since nothing found */

Option 2:
select ID from CITIES where CITY = :NEWCITY into :NEWID;
if ( NEWID is not null ) then suspend;
/* Do insert operation since nothing found */

Option 3:
select ID from CITIES where CITY = :NEWCITY into :NEWID;
if ( NEWID > 0 ) then suspend;
/* Do insert operation since nothing found */

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

Or is there yet another better construct?

In typing this out I think I've answered my own question - I would say Option 1 is the least efficient as it has two selects, and while Option 4 is expressed the simplest it may result in side-effects - so choose either Option 2 or 3 depending on personal preference. So I'm leaning towards Option 3 unless I'm advised differently.

--
Daniel