Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Can I use Update usin WITH <tab> AS

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try the MERGE statement.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DBFinder

    Do I have to tell him to use MERGE instead of UPDATE.
    I think you should tell him to go read a manual.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •