The statement:

merge into nullid.animals_et_in t1 using is3.animals t2
on t1.sire_assoc=t2.assoc and t1.sire_prefix=t2.prefix and
t1.sire_regnum=t2.regnum
when matched then update set t1.sire_bhid=t2.bhid
when not matched then update set t1.sire_bhid=0
go

gets the error message:

Error: An unexpected token "when not matched then" was found following
"T1.SIRE_BHID=T2.BHID". Expected tokens may include: "<merge_when_spec1>".

which I don't understand. I read the syntax diagrams and explanations
and thought that would be valid syntax. Any explanation or reference
would greatly appreciated. DB2 UDB V8.1.5 under Linux.

In article <9qivd.166$iD3.162@fe05.lga>, Bob Stearns (rstearns1241
@charter.net) says...
> The statement:
>
> merge into nullid.animals_et_in t1 using is3.animals t2
> on t1.sire_assoc=t2.assoc and t1.sire_prefix=t2.prefix and
> t1.sire_regnum=t2.regnum
> when matched then update set t1.sire_bhid=t2.bhid
> when not matched then update set t1.sire_bhid=0
> go
>
> gets the error message:
>
> Error: An unexpected token "when not matched then" was found following
> "T1.SIRE_BHID=T2.BHID". Expected tokens may include: "<merge_when_spec1>".
>
> which I don't understand. I read the syntax diagrams and explanations
> and thought that would be valid syntax. Any explanation or reference
> would greatly appreciated. DB2 UDB V8.1.5 under Linux.
>

You are not allowed to do an insert:

NOT MATCHED
Indicates the operation to be performed on the rows where the ON
search condition is false or unknown. Only INSERT or signal-statement
can be specified after THEN.

Gert van der Kooij wrote:
> In article <9qivd.166$iD3.162@fe05.lga>, Bob Stearns (rstearns1241
> @charter.net) says...
>
>>The statement:
>>
>>merge into nullid.animals_et_in t1 using is3.animals t2
>> on t1.sire_assoc=t2.assoc and t1.sire_prefix=t2.prefix and
>>t1.sire_regnum=t2.regnum
>> when matched then update set t1.sire_bhid=t2.bhid
>> when not matched then update set t1.sire_bhid=0
>>go
>>
>>gets the error message:
>>
>>Error: An unexpected token "when not matched then" was found following
>>"T1.SIRE_BHID=T2.BHID". Expected tokens may include: "<merge_when_spec1>".
>>
>>which I don't understand. I read the syntax diagrams and explanations
>>and thought that would be valid syntax. Any explanation or reference
>>would greatly appreciated. DB2 UDB V8.1.5 under Linux.
>>
>
>
> You are not allowed to do an insert:
>
> NOT MATCHED
> Indicates the operation to be performed on the rows where the ON
> search condition is false or unknown. Only INSERT or signal-statement
> can be specified after THEN.
... which when one thinks about it is the only option... How could DB2
update a row that it didn't find to begin with?

Cheers
Serge

Serge Rielau wrote:
> Gert van der Kooij wrote:
>
>> In article <9qivd.166$iD3.162@fe05.lga>, Bob Stearns (rstearns1241
>> @charter.net) says...
>>
>>> The statement:
>>>
>>> merge into nullid.animals_et_in t1 using is3.animals t2
>>> on t1.sire_assoc=t2.assoc and t1.sire_prefix=t2.prefix and
>>> t1.sire_regnum=t2.regnum
>>> when matched then update set t1.sire_bhid=t2.bhid
>>> when not matched then update set t1.sire_bhid=0
>>> go
>>>
>>> gets the error message:
>>>
>>> Error: An unexpected token "when not matched then" was found
>>> following "T1.SIRE_BHID=T2.BHID". Expected tokens may include:
>>> "<merge_when_spec1>".
>>>
>>> which I don't understand. I read the syntax diagrams and explanations
>>> and thought that would be valid syntax. Any explanation or reference
>>> would greatly appreciated. DB2 UDB V8.1.5 under Linux.
>>>
>>
>>
>> You are not allowed to do an insert:
>>
>> NOT MATCHED
>> Indicates the operation to be performed on the rows where the ON
>> search condition is false or unknown. Only INSERT or signal-statement
>> can be specified after THEN.
>
> .. which when one thinks about it is the only option... How could DB2
> update a row that it didn't find to begin with?
>
> Cheers
> Serge
Doh!! I continue to think of 'not matched' meaning t1 not matched. It is
of course t2 which is not matched. Syntax like 'into not matched' and
'using not matched' (these choices are abominable, but it's late) would
be useful and cover all 3 cases.

Bob Stearns wrote:
> Serge Rielau wrote:
>
>> Gert van der Kooij wrote:
>>
>>> In article <9qivd.166$iD3.162@fe05.lga>, Bob Stearns (rstearns1241
>>> @charter.net) says...
>>>
>>>> The statement:
>>>>
>>>> merge into nullid.animals_et_in t1 using is3.animals t2
>>>> on t1.sire_assoc=t2.assoc and t1.sire_prefix=t2.prefix and
>>>> t1.sire_regnum=t2.regnum
>>>> when matched then update set t1.sire_bhid=t2.bhid
>>>> when not matched then update set t1.sire_bhid=0
>>>> go
>>>>
>>>> gets the error message:
>>>>
>>>> Error: An unexpected token "when not matched then" was found
>>>> following "T1.SIRE_BHID=T2.BHID". Expected tokens may include:
>>>> "<merge_when_spec1>".
>>>>
>>>> which I don't understand. I read the syntax diagrams and
>>>> explanations and thought that would be valid syntax. Any
>>>> explanation or reference would greatly appreciated. DB2 UDB V8.1.5
>>>> under Linux.
>>>>
>>>
>>>
>>> You are not allowed to do an insert:
>>>
>>> NOT MATCHED
>>> Indicates the operation to be performed on the rows where the ON
>>> search condition is false or unknown. Only INSERT or signal-statement
>>> can be specified after THEN.
>>
>>
>> .. which when one thinks about it is the only option... How could DB2
>> update a row that it didn't find to begin with?
>>
>> Cheers
>> Serge
>
> Doh!! I continue to think of 'not matched' meaning t1 not matched. It is
> of course t2 which is not matched. Syntax like 'into not matched' and
> 'using not matched' (these choices are abominable, but it's late) would
> be useful and cover all 3 cases.
DB2 has an (obvious) extension to the standard allowing you to add
predicates to MATCHED and NOT MATCHED. So you can indeed have more than
one UPDATE, DELETE and/or INSERT clause.
The ON-clause partitiones teh set into MATHCED and NOT MATCHED the
predicates sub-partitioned into the various phases.

Not sure if that covers what you're asking for.
Cheers
Serge