473,288 Members | 1,710 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

MERGE syntax error

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
4 5283
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Karthik | last post by:
Hi, We have setup a publisher and a distributor in SQL 2000 running SP3. Alongwith this we have setup a merge replication agent and its running successfully inside the same network. I allow...
8
by: Squirrel | last post by:
Hi everyone, I've created a mail merge Word doc. (using Office XP) , the data source is an Access query. Functionality I'm attempting to set up is: User sets a boolean field to true for...
3
by: Andy Davis | last post by:
I have set up a mail merge document in Word 2003 which gets its data from my Access 2000 database. I want to set up a button on a form that: 1. runs the query to provide the dat for the merge...
3
by: Bob Stearns | last post by:
We have a very poorly performing MERGE statement (an hour or more on tables of ~10000 and ~100000). This may require building temporary tables with appropriate indexes, but I thought I would ask...
4
by: marklawford | last post by:
Hi guys, I'm getting the following error message when trying to run a MERGE statement I'm putting together. The syntax looks right to me But i must be missing something. The "srce" table...
0
by: mayankaerry | last post by:
Hi, I am facing an error on calling Datset update method. Following is my scenario: There are Three Excel Files Test1.xls,Test2.xls and Test3.xls. There is data in Test1.xls as under: B
6
by: amitabh.mehra | last post by:
I want to trap errors (RI errors), if any, that might turn up during merge in DB2. Is there some feature like the one in Oracle: MERGE INTO... WHEN MATCHED THEN UPDATE... WHEN NOT MATCHED THEN...
1
by: Esther Lane | last post by:
Hello! First off, many many thanks to Albert who wrote the Mail Merge code for MS Access I am using. It has been working beautifully for a few years. However, my client just (without notice!)...
3
by: subramanian100in | last post by:
Consider the following program: #include <cstdlib> #include <iostream> #include <set> #include <map> #include <algorithm> #include <iterator> #include <utility>
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.