MERGE syntax error

[Bob Stearns]

The statement:

merge into nullid.animals_ et_in t1 using is3.animals t2
on t1.sire_assoc=t 2.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=T 2.BHID". Expected tokens may include: "<merge_when_sp ec1>".

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.

[Gert van der Kooij]

In article <9q************ ***@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=t 2.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=T 2.BHID". Expected tokens may include: "<merge_when_sp ec1>".

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.

[Serge Rielau]

Gert van der Kooij wrote:

In article <9q************ ***@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=t 2.assoc and t1.sire_prefix= t2.prefix and
t1.sire_regnu m=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_sp ec1>".

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

[Bob Stearns]

Serge Rielau wrote:

Gert van der Kooij wrote:

In article <9q************ ***@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=t 2.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=T 2.BHID". Expected tokens may include:
"<merge_when_sp ec1>".

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.

[Serge Rielau]

Bob Stearns wrote:

Serge Rielau wrote:

Gert van der Kooij wrote:

In article <9q************ ***@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=t 2.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=T 2.BHID". Expected tokens may include:
"<merge_when_sp ec1>".

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