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
