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