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]