Results 1 to 14 of 14
  1. #1
    Join Date
    May 2012
    Location
    Canada
    Posts
    14

    Question Unanswered: Problems on update

    I tried updating the column name projectid in actualhrs_staging table with the column projectid in VW_PC_PBEXTRACT view.
    i tried these methods but failed with all.

    1)
    Code:
    update actualhrs_staging ahs
    set projectid = (select projectid from VW_PC_PBEXTRACT as clarproj
    		where clarproj.clarityid = ahs.clarityid)
    where loaddate = '2012-07-30'
    the following error occurs :
    SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row.

    2)
    Code:
    merge into actualhrs_staging ahs
    using (select projectid, clarityid 
             from VW_PC_PBEXTRACT 
             where clarityid in
                                    (select distinct clarityid 
                                     from actualhrs_staging 
                                     where loaddate ='2012-07-30')) new
    on ahs.clarityid = new.clarityid
    when matched then
     update set
    	ahs.projectid = new.projectid 
    	 else ignore
    following error occurs when i tried above sql :
    SQL0788N The same row of target table "RPD.ACTUALHRS_STAGING

    3)
    In this method i tried using for loop in stored procedure so that i can update one row at a time, but this also failed.
    I am not getting any error with this method, but instead getting wrong result.
    Here's a stored procedure i created.
    Code:
    CREATE PROCEDURE updateclaritytable
    
    BEGIN
    DECLARE PROJID INTEGER;
    DECLARE PROJTEAMID INTEGER;
    DECLARE STEAMID INTEGER;
    DECLARE SUPPCODE VARCHAR(50);
    DECLARE CLAR VARCHAR(50);
    SET CURRENT SCHEMA RPD;
    
    FOR v_row AS select projectid, clarityid, projectteamid,siteteam,suppliercode 
                        from VW_PC_PBEXTRACT as clarproj
    		    where clarproj.clarityid in 
                                                         (select distinct clarityid 
                                                          from actualhrs_staging 
                                                          where loaddate = '2012-07-30')
        DO	 	 	 
            SET PROJID = v_row.projectid;
    	SET PROJTEAMID = v_row.projectteamid;
    	SET STEAMID = v_row.siteteam;
    	SET SUPPCODE = v_row.suppliercode;
        	SET CLAR = v_row.clarityid;
    	                      
    END FOR;
    	UPDATE ACTUALHRS_STAGING  
    	SET projectid = PROJID
    	where loaddate = '2012-07-30'
    	and clarityid = CLAR;    
    END
    After successful calling of the stored procedure, i found that all the rows are being updated with single value i.e. projectid = 100002469. This is not a desired result as every row should have different projectid with the sentinel value is actualhrs_staging.clarityid = VW_PC_PBEXTRACT.clarityid.

    Thanks any help will be appreciated.

    Jas
    Last edited by jassi; 08-03-12 at 14:06.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    See the result of ...
    Code:
    SELECT clarityid
         , COUNT(*)                  AS count_rows
         , COUNT(DISTINCT projectid) AS dist_projectid
     FROM  VW_PC_PBEXTRACT
     GROUP BY
           clarityid
     HAVING
           COUNT(*) > 1
     FETCH FIRST 20 ROWS ONLY
    ;
    Note: adjust value of FETCH FIRST 20 ROWS ONLY as you like.

  3. #3
    Join Date
    May 2012
    Location
    Canada
    Posts
    14
    Here's a result of sql

    clarityid count_rows dist_projectid
    00278/00001002 6 1
    00278/20100034 10 1
    00278/W2081958 46 1
    00278/W2176973 18 1
    00327/W2021001 4 1
    00327/W2023432 16 1
    00327/W2081875 10 1
    00327/W2081958 276 1
    00327/W2172868 16 1
    00327/W2176973 72 1
    00327/W2179710 88 1
    00327/W2180455 14 1
    00327/W2183482 26 1
    00383/W2010047 54 1
    00383/W2172750 8 1
    00415/00001000 19 1
    00415/00001002 24 1
    00415/00001004 12 1
    00415/00001005 84 1
    00415/00001006 2 1

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try ...
    Code:
    UPDATE actualhrs_staging AS ahs
       SET projectid
           = (SELECT projectid
               FROM  VW_PC_PBEXTRACT AS cp
               WHERE cp.clarityid = ahs.clarityid
               FETCH FIRST 1 ROW ONLY
             )
     WHERE loaddate = '2012-07-30'
    ;

  5. #5
    Join Date
    May 2012
    Location
    Canada
    Posts
    14
    the query is working...thank you

    other question
    can you please tell me that what is the error in the stored procedure that i created in 3).

    why the projectid is updated with the single value only?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jassi View Post

    why the projectid is updated with the single value only?
    Because you execute the UPDATE once outside the loop?
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guessed that because the UPDATE was done outside FOR loop, so last value was used in the UPDATE.

    But, you specified "WHERE ... and clarityid = CLAR" in the UPDATE.
    It seems contradiction with "all the rows are being updated ...".
    I don't know why?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It might be better to confirm that the following query returns no row.

    Code:
    SELECT clarityid
         , COUNT(*)                  AS count_rows
         , COUNT(DISTINCT projectid) AS dist_projectid
     FROM  VW_PC_PBEXTRACT
     GROUP BY
           clarityid
     HAVING
           COUNT(DISTINCT projectid) > 1
    ;

  9. #9
    Join Date
    May 2012
    Location
    Canada
    Posts
    14
    Because you execute the UPDATE once outside the loop?
    That's giving expected result.
    That's how i started when i created stored procedure, but earlier i don't know why i was getting wrong result.



    I guessed that because the UPDATE was done outside FOR loop, so last value was used in the UPDATE.
    But, you specified "WHERE ... and clarityid = CLAR" in the UPDATE.
    It seems contradiction with "all the rows are being updated ...".
    I don't know why?
    yes that update should be in the for loop/

    All rows are being updated.Why contradiction as it is supposed to update all the rows of the table with PROJID.

    It might be better to confirm that the following query returns no row.

    Code:

    SELECT clarityid
    , COUNT(*) AS count_rows
    , COUNT(DISTINCT projectid) AS dist_projectid
    FROM VW_PC_PBEXTRACT
    GROUP BY
    clarityid
    HAVING
    COUNT(DISTINCT projectid) > 1
    ;

    the following result is returned

    00419/00001004 303 2
    00419/W2180455 16 2


    ...

    Thanks
    Jas

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What was the result of
    Code:
    SELECT clarityid
         , COUNT(*)                  AS count_rows
         , COUNT(DISTINCT projectid) AS dist_projectid
         , MIN(projectid)            AS min_projectid
         , MAX(projectid)            AS max_projectid
     FROM  VW_PC_PBEXTRACT
     GROUP BY
           clarityid
     HAVING
           COUNT(DISTINCT projectid) > 1
    ;

  11. #11
    Join Date
    May 2012
    Location
    Canada
    Posts
    14
    What was the result of
    Code:

    SELECT clarityid
    , COUNT(*) AS count_rows
    , COUNT(DISTINCT projectid) AS dist_projectid
    , MIN(projectid) AS min_projectid
    , MAX(projectid) AS max_projectid
    FROM VW_PC_PBEXTRACT
    GROUP BY
    clarityid
    HAVING
    COUNT(DISTINCT projectid) > 1
    ;

    here's a result
    clarityid count_rows dist_projectid min_projectid max_projectid
    00419/00001004 303 2 100001824 100002542
    00419/W2180455 16 2 100002030 100002381

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Which of min_projectid or max_projectid do you want to set to projectid of actualhrs_staging?

  13. #13
    Join Date
    May 2012
    Location
    Canada
    Posts
    14
    let it be max_projectid

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If to set max_projectid was essential,
    you may want to use MAX function instead of "FETCH FIRST 1 ROW ONLY".
    But, performance may get down.

    Code:
    UPDATE actualhrs_staging AS ahs
       SET projectid
           = (SELECT MAX(projectid)
               FROM  VW_PC_PBEXTRACT AS cp
               WHERE cp.clarityid = ahs.clarityid
            /* FETCH FIRST 1 ROW ONLY */
             )
     WHERE loaddate = '2012-07-30'
    ;

    When I asked the result of the last query(which specified "HAVING COUNT(DISTINCT projectid) > 1"),
    I expected one of MIN(projectid) or MAX(projectid) might be some trivial value(s) to be exclude easily that value.
    But, reality seems not so easy.

Posting Permissions

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