Re: [firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-31 Thread Gordon Niessen
On 10/20/2011 11:28 AM, gastrocus wrote: > > > > --- In firebird-support@yahoogroups.com > , Helen Borrie > wrote: > > > > >What is the analogous way to achieve this in Firebird (2.5) ? > > > > INSERT INTO emp (fruits) values ('mango') > > where not exi

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-25 Thread Milan Babuskov
gastrocus wrote: > INSERT INTO T1 (type, name, sysid, flag) values (1, 'Z', 1, 0) > WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z') You can apply select to rdb$database table which always returns one row: INSERT INTO T1 (type, name, sysid, flag) SELECT 1, 'Z', 1, 0 FROM RDB$DATAB

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-21 Thread Gordon Niessen
Not sure why this did not post the first time. See below: On 10/20/2011 11:28 AM, gastrocus wrote: > > > > --- In firebird-support@yahoogroups.com > , Helen Borrie > wrote: > > > > >What is the analogous way to achieve this in Firebird (2.5) ? > > > >

[firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread Svein Erling Tysvær
>Just realized you can make it even easier: > > merge >into emp >using rdb$database >on emp.fruits = 'mango' >when not matched then insert (fruits) values ('mango') An alternative (just an alternative available on all Firebird - and probably many InterBase - versions, the MERGE c

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread Paul Vinkenoog
Hi Ed, > > merge > > into emp > > using (select 'mango' fruits from rdb$database) src > > on emp.fruits = src.fruits > > when not matched then insert (fruits) values ('mango') > > Nice trick! That seems to work. Just realized you can make it even easier: merge into emp

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread Helen Borrie
At 10:34 AM 21/10/2011, gastrocus wrote: >--- In firebird-support@yahoogroups.com, "gastrocus" wrote: > >> > >> > merge into t1 tab2 >> > using t1 tab1 >> > on tab1.name = tab2.name and tab1.name = 'Z' >> > when not matched then >> > insert (type, name, sysid, flag) values (1, 'Z', 1, 0)

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread Helen Borrie
At 05:28 AM 21/10/2011, you wrote: >--- In firebird-support@yahoogroups.com, Helen Borrie 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

[firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread gastrocus
--- In firebird-support@yahoogroups.com, Paul Vinkenoog wrote: > > > So, when I tested it on a table which had 100 rows (non matching the > > 'Z' test), it inserted 100 new rows, each with a '' name. > > Yes, if you want to use this trick, the _source_ table should have 1 row. > > So

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread Paul Vinkenoog
Hi, > > > merge into t1 tab2 > > > using t1 tab1 > > > on tab1.name = tab2.name and tab1.name = 'Z' > > > when not matched then > > > insert (type, name, sysid, flag) values (1, 'Z', 1, 0) > Whoops, I spoke too soon as I had tested it on a table with 1 row first and > it appeared to work

[firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread gastrocus
--- In firebird-support@yahoogroups.com, "gastrocus" wrote: > > > > --- In firebird-support@yahoogroups.com, "Helen" wrote: > > > > > Would you like to try this: > > > > merge into t1 tab2 > > using t1 tab1 > > on tab1.name = tab2.name and tab1.name = 'Z' > > when not matched then > > in

[firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread gastrocus
--- In firebird-support@yahoogroups.com, "Helen" wrote: > > Would you like to try this: > > merge into t1 tab2 > using t1 tab1 > on tab1.name = tab2.name and tab1.name = 'Z' > when not matched then > insert (type, name, sysid, flag) values (1, 'Z', 1, 0) > That works like a charm! T

[firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread Helen
At 05:28 AM 21/10/2011, you wrote: --- In firebird-support@yahoogroups.com, Helen Borrie 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

Re: [firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread Ismael L. Donis Garcia
Message - From: gastrocus To: firebird-support@yahoogroups.com Sent: Thursday, October 20, 2011 12:28 PM Subject: [firebird-support] Re: How to insert only if a matching row does not exist? --- In firebird-support@yahoogroups.com, Helen Borrie wrote: > > >Wh

[firebird-support] Re: How to insert only if a matching row does not exist?

2011-10-20 Thread gastrocus
--- In firebird-support@yahoogroups.com, Helen Borrie 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 t