Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: Common Table Expression in Update statement

    Hi All

    I have an update statement in which i am using a CTE (Common table Expression), but when i try to run its giving me syntax error. is CTE not supported inside update statements?. My db is Db2 V9.5 on Aix

    My query is like the below.

    update db2.tablename set col1=123 , col2='P'

    where col3 in
    ( with temp 1 as (select a.col1,b.col2 from tableA A, tableB B where .....some conditions)
    ,
    temp2 as (select a.col1 from tableA where ...some different conditions here)

    select col1 from temp1 where temp1.col1=temp2.col1 where ....
    )


    SQL0104N An unexpected token "as" was found following "ol3 IN ( with temp1".
    Expected tokens may include: "JOIN". SQLSTATE=42601

    can some one please tell me why i am getting this error?

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    "...with temp 1 as..."

    is there a blanc between "temp" and "1" ? What happens if you omit it ?
    Last edited by umayer; 08-28-09 at 03:43.

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    actually there is no space in my query between temp and 1. it a typo while posting here. it's as temp1 in my original query.

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    such syntax is not supported, use normal joins instead in subquery
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sometimes, you can use nested table expression insted of common table expression.

    For example:

    where col3 in
    (
    select temp1.col1
    from (select a.col1,b.col2 from tableA A, tableB B where .....some conditions) AS temp1,
    (select a.col1 from tableA where ...some different conditions here) AS temp2
    where temp1.col1=temp2.col1 ....
    )

  6. #6
    Join Date
    Dec 2008
    Posts
    76
    Or you could use a merge statement
    RD

  7. #7
    Join Date
    Jul 2010
    Posts
    3

Posting Permissions

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