--- In firebird-support@yahoogroups.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.