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

merge statement - why it is failing

P: n/a
MERGE INTO MyTable as nd USING
(with tex (ID, acc_num) as ( values ('1','acc232'), ('2','acc8993'),
('3','acc543') )
select ID, acc_num from tex
) as T
on nd.id = T.ID
WHEN NOT MATCHED THEN
INSERT (ID) VALUES (T.ID) ;

Error message:
"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "as" was found following "th tex (ID,
acc_num)".
Expected tokens may include: "JOIN". SQLSTATE=42601

SQL0104N An unexpected token "as" was found following "th tex (ID,
acc_num)". Expected tokens may include:
"JOIN ".
"

What am I doing wrong..?

Thanks,

Oct 2 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
an*************@gmail.com wrote:
MERGE INTO MyTable as nd USING
(with tex (ID, acc_num) as ( values ('1','acc232'), ('2','acc8993'),
('3','acc543') )
select ID, acc_num from tex
) as T
on nd.id = T.ID
WHEN NOT MATCHED THEN
INSERT (ID) VALUES (T.ID) ;

Error message:
"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "as" was found following "th tex (ID,
acc_num)".
Expected tokens may include: "JOIN". SQLSTATE=42601

SQL0104N An unexpected token "as" was found following "th tex (ID,
acc_num)". Expected tokens may include:
"JOIN ".
"

What am I doing wrong..?
The WITH clause isn't supported in subqueries.
But why so complicated?
MERGE INTO MyTable as nd
USING (values ('1','acc232'), ('2','acc8993'),
('3','acc543') ) AS T(ID, acc_num)
....

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Oct 2 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.