SEO

#1 (**permalink**) 02-27-2008, 01:06 PM

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,

#2 (**permalink**) 02-27-2008, 01:06 PM

annecarterfredi@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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

merge statement - why it is failing