On 10-11-2013 09:29, Daniel L. Miller wrote:

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

I'd go for a MERGE
(http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-merge.html)
or maybe UPDATE OR INSERT
(http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-update-or-insert.html).

BTW: Unless you explicitly want to create a selectable stored procedure,
I don't think you need (nor want) to use SUSPEND in this stored procedure.

--
Mark Rotteveel