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
