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

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

    Hi guys,

    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

    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
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >DB2 is pointing an error on update.
    That is nice, but this is an Oracle forum.
    The DB2 forum is down the hall & 2nd door on the right.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by anacedent
    That is nice, but this is an Oracle forum.
    The DB2 forum is down the hall & 2nd door on the right.
    this is nice again !

    Your directions are cool.

    By the way, does WITH table AS works with UPDATE in ORACLE ??


    SQLs are mostly standardized.

    DBFinder

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >By the way, does WITH table AS works with UPDATE in ORACLE
    When all else fails, Read The Fine Manual

    SELECT

    subquery_factoring_clause

    The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.

    You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Thanks anacedent,

    For quick response; I was still at next door :: down the hall & 2nd door on the right

    I appreciate your help.

    DBFinder

  6. #6
    Join Date
    Nov 2003
    Posts
    2,932
    Provided Answers: 12
    Quote Originally Posted by DBFinder
    SQLs are mostly standardized.
    Definitely not. There is a standard, but apart from the most basic syntax, the individual DBMS differ quite substantially. And the common table expression (CTE) you are referring to is a very good example. Some implement that, some implement it only partially, some implement it with the wrong syntax (thinking of RECURSIVE keyword) and some don't implement it.

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    thanks shammat

Posting Permissions

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