At 10:34 AM 21/10/2011, gastrocus wrote: >--- In firebird-support@yahoogroups.com, "gastrocus" <gastrocus@...> wrote: > >> > >> > 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) >> > >> >> That works like a charm! >> Thanks Helen. > >Whoops, I spoke too soon as I had tested it on a table with 1 row first and it >appeared to work. > >The above statement seems to do a separate insert for each row in table which >does not match the test. > >So, when I tested it on a table which had 100 rows (non matching the 'ZZZZZ' >test), it inserted 100 new rows, each with a 'ZZZZ' name.
Oops, sorry about that! I couldn't test it as my 2+ servers are otherwise occupied at the moment. Will shut up in future when I can't test something abstruse. The rdb$database trick is a workaround so it's good to know it worked out for you. As Paul (and others) commented though, a SP would be more elegant. I shied away from suggesting it because you had said you wanted to script this operation. If the plan was run a DML script through isql, you might want to rethink the methodology, if this is a thing you have to do regularly...a SP or a GTT (or both in combination) would serve your purpose more effectively. ./hb >------------------------------------ > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > >Visit http://www.firebirdsql.org and click the Resources item >on the main (top) menu. Try Knowledgebase and FAQ links ! > >Also search the knowledgebases at http://www.ibphoenix.com > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >Yahoo! Groups Links > > >
