Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    39

    Unanswered: Upadate using With Clause

    Hi

    Can you please let me know syntax for using Update using with Clause.I am able to use insert with With Clause.But Update is not working .

    Please help

    Thanks

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Kindly explain in detail why you want to UPDATE using the WITH clause only and why not a simple UPDATE with JOIN......

    It may be possible that you don't require the WITH clause in the first place.
    IBM Certified Database Associate, DB2 9 for LUW

  3. #3
    Join Date
    May 2008
    Posts
    39
    Actually I am using 10 temporary tables and it involves a complicated logic.
    So I cannot avoid using with clause.

    Please advise

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You cannot use "WITH common table expression" clause in an UPDATE statement.

    Please see syntax diagram of UPDATE statement
    in "DB2 Version 9 for Linux, UNIX, and Windows SQL Reference Volume 2".

  5. #5
    Join Date
    May 2008
    Posts
    39
    Thanks so much.
    As mentioned I have used with clause with 10 temp tables...
    In my where condition of select clause I have a condition as mentioned below:

    where (( x.account_sk =tm.account_sk and y.ACCOUNT_SK=tm.account_sk ) ---------------------(1)
    or
    x1.account_sk =tdt.account_sk and y1.account_sk=lft.account_sk ))---------(2)

    If I give condition (1) ,(2) seperately it works fine,when I include or in between ,query doesnt return any result.runs for longer time

    There is no cartesian product.

    Please help

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the MERGE statement. This may be more suitable for you.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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