--- 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')

>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 :(

Not strange - I put you wrong there. Sorry! Of course, INSERT won't accept a WHERE clause.

>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')

>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

UPDATE OR INSERT - I get that wrong 50% of the time. ;-)

>prior to posting but I do not want to UPDATE if there is a match so >I didn't think this applied.

Would you like to try this:

merge into t1 tab2
using t1 tab1
on tab1.name = tab2.name and tab1.name = 'ZZZZZ'
when not matched then
insert (type, name, sysid, flag) values (1, 'ZZZZZ', 1, 0)

./heLen