Can I use Update usin WITH <tab> AS

DBFinder · #1 (**permalink**) 07-22-09, 14:37

Hi guys,

some times I use WITH as

Code:

with tmp (name,work) as ( select empno,job from employee) select * from tmp ;

I am wondring how can I update a table by using contents of temprary table.

e.g

Code:

with tmp (name,work) as ( select empno,job from employee) update xyz 
                     set lname= (select name from tmp fetch first 1 row only) ;

I haven't seen any example like this , but there must be some way to do it.

DB2 is pointing an error on update.

I tried to search documentation, but did not find any example using WITH and UPDATE together.

Please help.

DBFinder

n_i · #2 (**permalink**) 07-22-09, 14:48

Try the MERGE statement.

DBFinder · #3 (**permalink**) 07-22-09, 16:29

Actully developer has written a SQL with update.

Do I have to tell him to use MERGE instead of UPDATE.

I can try MERGE for simple example with no problem.