Results 1 to 3 of 3
  1. #1
    Join Date
    May 2012
    Posts
    3

    Post Unanswered: Update query to update multiple columns and rows

    I have a requirement to update mulitiple column and row in a table from another table.

    Please help me on this.

    i have two tables STG_EFDE and FDE_BTCH_TEST.i want to update the FDE_BTCH_TEST table.Please find the query below.
    Update PORTALEFDE.FDE_BTCH_TEST B
    SET (B.BTCH_NUM,
    B.CLM_ORIG_CNT,
    B.RCPT_TMSP,
    B.CLM_FORM_IND,
    B.CLERK_ID,
    B.WRK_PL_CD,
    B.HOMPL_CD,
    B.UNIT_ID,
    B.RPT_RGN_CD,
    B.BTCH_AGE_CNT,
    B.CLM_DIFF_CNT,
    B.CLM_HELD_CNT,
    B.CLM_NOT_ENT_CNT,
    B.CLM_RLSE_CNT,
    B.CLM_CURR_CNT,
    B.CLM_ENT_CNT,
    B.CLM_DEL_CNT,
    B.BTCH_ACTV_DT,
    B.BTCH_RCVD_DT,
    B.BTCH_RLSE_DT,
    B.ACTV_RCVD_DAY_CNT,
    B.CLM_NOT_STSFD_CNT,
    B.STAT_DESC,
    B.CREAT_USER_ID,
    B.CREAT_USER_NM,
    B.CREAT_USER_TMSP,
    B.UPDT_USER_ID,
    B.UPDT_USER_NM,
    B.UPDT_USER_TMSP,
    B.WRK_BASKET_NM,
    B.BTCH_FILL_STAT_DESC)
    =
    (SELECT
    A.BATCHNUMBER,
    CAST (A.ORIGINALCLAIMCOUNT as smallint),
    A.RECEIPTDATE,
    (rtrim(ltrim(replace(char(A.CLAIMFORMINDICATOR),'. ', '')))),
    A.CLERKID,
    (rtrim(ltrim(replace(char(A.WORKPLANCODE),'.', '')))),
    (rtrim(ltrim(replace(char(A.HOMEPLANCODE),'.', '')))),
    A.UNITID,
    (rtrim(ltrim(replace(char(A.REPORTINGREGION),'.', '')))),
    CAST (A.BATCHAGE as smallINT),
    CAST(A.BATCHCOUNTADJUST as smallINT),
    CAST(A.CLAIMSHELD as smallINT),
    CAST(A.CLAIMSNOTENTERED as smallINT),
    CAST(A.CLAIMSRELEASED as smallINT),
    CAST(A.CURRENTCLAIMCOUNT as smallINT),
    CAST(A.TOTALCLAIMSENTERED as smallINT),
    CAST(A.DELETEDCLAIMCOUNT as smallINT),
    date(substr(coalesce(A.dateactivated,'19000101'),1 ,4)||'-'||substr(coalesce(A.dateactivated,'19000101'),5,2 )||'-'||substr(coalesce(A.dateactivated,'19000101'),7,2 )) as DateActivated,
    date(substr(coalesce(A.datereceived,'19000101'),1, 4)||'-'||substr(coalesce(A.datereceived,'19000101'),5,2) ||'-'||substr(coalesce(A.datereceived,'19000101'),7,2) ) as DateReceived,
    date(substr(coalesce(A.releasedate,'19000101'),1,4 )||'-'||substr(coalesce(A.releasedate,'19000101'),5,2)| |'-'||substr(coalesce(A.releasedate,'19000101'),7,2)) as ReleaseDate,
    CAST((CASE WHEN A.BATCHAGEFORREPORTS IS NULL THEN '0'
    WHEN A.BATCHAGEFORREPORTS='N/A' THEN '0'
    ELSE A.BATCHAGEFORREPORTS END) AS SMALLINT) AS ACTV_RCVD_DAY_CNT,
    CAST(A.PXCOVEREDCOUNTUNSATISFIED as smallint),
    A.PYSTATUSWORK,
    A.PXCREATEOPERATOR,
    A.PXCREATEOPNAME,
    A.PXCREATEDATETIME,
    A.PXUPDATEOPERATOR,
    A.PXUPDATEOPNAME,
    A.PXUPDATEDATETIME,
    A.PYORIGUSERWORKGROUP,
    A.BATCHSTATUS

    FROM PORTALEFDE.STG_EFDE A
    --INNER JOIN PORTALEFDE.FDE_BTCH_TEST B
    where B.WRK_PL_CD = (rtrim(ltrim(replace(char(A.WORKPLANCODE),'.', ''))))
    AND B.BTCH_NUM=A.BATCHNUMBER
    AND A.Claimid IS NULL
    AND B.UPDT_USER_TMSP>= '1900-01-01-00.00.00.0'
    AND B.UPDT_USER_TMSP<= '2012-05-17 00:00:00.0'
    AND B.CREAT_USER_TMSP <B.UPDT_USER_TMSP);

    i am getting this error:

    The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.. SQLCODE=-811, SQLSTATE=21000, DRIVER=4.11.69

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Always post your DB2-version and fixpack and operating-system name.

    You need to add more predicates to the WHERE clause (or more clauses on the inner join) , or a distinct clause, to ensure that the subselect returns a *single* row.

    Normally you need to understand the data model to get this right.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Make sure that the correlated
    Code:
        B.WRK_PL_CD = (rtrim(ltrim(replace(char(A.WORKPLANCODE),'.', ''))))
    AND B.BTCH_NUM=A.BATCHNUMBER
    AND A.Claimid IS NULL
    really refers to at most (and hopefully exactly) one single row of table A;
    and move
    Code:
        B.UPDT_USER_TMSP>= '1900-01-01-00.00.00.0'
    AND B.UPDT_USER_TMSP<= '2012-05-17 00:00:00.0'
    AND B.CREAT_USER_TMSP <B.UPDT_USER_TMSP
    out of the subquery since otherwise you will be updating all non-matching rows of B with all NULLs!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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