On 10/20/2011 11:28 AM, gastrocus wrote: > > > > --- In firebird-support@yahoogroups.com > <mailto:firebird-support%40yahoogroups.com>, Helen Borrie > <helebor@...> wrote: > > > > >What is the analogous way to achieve this in Firebird (2.5) ? > > > > INSERT INTO emp (fruits) values ('mango') > > where not exists (select 1 from emp where fruits = 'mango') > > Thanks for the quick reply. > > Strange... when I try to do this (via flamerobin or ibexpert) it does > not recognize the "Where" token (Dynamic SQL error -104, Token > unknown, where ). Must be something simple :( > > Here is what I am trying: > > I have a table like the following: > CREATE TABLE T1 ( > ID INTEGER NOT NULL, > TYPE INTEGER, > NAME VARCHAR(128), > SYSID INTEGER, > FLAG INTEGER > ); > > ID has a generator so I don't want to insert a value for it. > > Here is the query I tried > > INSERT INTO T1 (type, name, sysid, flag) values (1, 'ZZZZZ', 1, 0) > WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'ZZZZZ') > > It > > > > Look up INSERT OR UPDATE and also MERGE in the Language Reference > Update. One or both might do what you want, more efficiently than NOT > EXISTS. Not enough info here to guess what you're really going to do > with it. > > > > My application is really to write a script that will be run once on a > smallish dataset (on the order of 1000 rows) so performance isn't > really a big deal. > > Thanks for the pointer. I did look at INSERT OR UPDATE prior to > posting but I do not want to UPDATE if there is a match so I didn't > think this applied. > > > > INSERT INTO EMP SELECT 'mango' from RDB$DATABASE where not EXISTS (SELECT 1 from emp where fruits = 'mango');
or in your example: INSERT INTO T1 (type, name, sysid, flag) SELECT 1, 'ZZZZZ', 1, 0 FROM RDB$DATABASE WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'ZZZZZ'); -- Thanks, Gordon [Non-text portions of this message have been removed]
