At 05:28 AM 21/10/2011, you wrote:

>--- 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, too. ;-)

>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