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
>
>
>