By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,850 Members | 970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,850 IT Pros & Developers. It's quick & easy.

MERGE syntax error

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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=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.
Nov 12 '05 #2

P: n/a
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=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
Nov 12 '05 #3

P: n/a
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=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.
Nov 12 '05 #4

P: n/a
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=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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.