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

    Unanswered: Update with select

    UPdate IDM_DEV.COUNTRY_DIM set update_lineage_id
    =(
    SELECT
    A.DM_LINEAGE_ID
    FROM
    CO c,
    LINEAGE A
    WHERE
    A.PROCESS_NM = 'SP'
    AND NOT EXISTS ( SELECT
    A.DM_LINEAGE_ID
    FROM
    CON_DIM CON_DIM,
    LINEAGE A
    WHERE
    C.COUNTRY_CD=CON_DIM.COUNTRY_CD AND
    CON_DIM.COUNTRY_NM= C.COUNTRY_NM
    AND A.PROCESS_NM ='SP'))

    My requirement is I want to update the result of select query with an id...Since select gives mmore than 1 row,I get an error.

    [IBM][CLI Driver][DB2/LINUXX8664] SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000

    Basically I want an update with select statement which returns multiple.

    Please help

  2. #2
    Join Date
    Jul 2008
    Posts
    11
    You can not have many values in a single cell...

    Usually, one do that kind of update to denormalize a value, the syntax is:
    UPDATE A SET Col1 = (
    SELECT B.Col2 FROM B
    WHERE B.Keys = A.Keys AND B.Col2 is not null AND B.OtherExpr)
    WHERE EXISTS(
    SELECT 1 FROM B
    WHERE B.Keys = A.Keys AND B.Col2 is not null AND B.OtherExpr)

    Can you write down what this is supposed to do ?
    To geek or not to geek !

  3. #3
    Join Date
    May 2008
    Posts
    39

    Update with select

    I have two tables: A and B

    B is my target table.A is source table.I have to select rows in source table which got updated and the same rows in target which has to be updated.
    I am doing that using select clause

    SELECT
    A.DM_LINEAGE_ID
    FROM
    CO c,
    LINEAGE A
    WHERE
    A.PROCESS_NM = 'SP'
    AND NOT EXISTS ( SELECT
    A.DM_LINEAGE_ID
    FROM
    CON_DIM CON_DIM,
    LINEAGE A
    WHERE
    C.COUNTRY_CD=CON_DIM.COUNTRY_CD AND
    CON_DIM.COUNTRY_NM= C.COUNTRY_NM
    AND A.PROCESS_NM ='SP'))
    Now,I have an audit table which has an id value.I have to update the target table with the id value....

  4. #4
    Join Date
    Jul 2008
    Posts
    11
    Quote Originally Posted by usDB2
    I have an audit table which has an id value.I have to update the target table with the id value....
    Your SQL looks bad because you do not have join criteria between your LINEAGE table and the others
    To geek or not to geek !

  5. #5
    Join Date
    May 2008
    Posts
    39
    Its beacuse source table has no relationship with Lineage ..

  6. #6
    Join Date
    Jul 2008
    Posts
    11
    then it can not work...
    To geek or not to geek !

Posting Permissions

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