Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    36

    Unanswered: Updating a column value with another tables column value

    Dear Experts,

    Im writing a PLSQL procedure to update a column value based on another tables condition.

    But my query is not working.

    Specification is :

    Update NewEntityID value as follows:

    Tbl_Entity_Tmp01.NewEntityID = Tbl_Matching_Tmp01.Code2

    Criteria:
    Tbl_Entity_Tmp01.REC_DT = Tbl_Matching_Tmp01.REC_DT1
    AND
    Tbl_Matching_Tmp01.VMatchingPer >= 96%




    CREATE OR REPLACE PROCEDURE SYSTEM.Fn_UPDATE_EntityID_Tmp_01
    as
    cursor c1 is
    SELECT * FROM Tbl_Matching_Tmp01 WHERE TRIM(RECORDTYPE) ='Entity' And TRIM(FUNCTIONNAME) ='Fn_Matching_Entity_Tmp_00' ;--ORDER BY AUTOID;

    -- TYPE dept_tab_type IS TABLE OF c1%ROWTYPE;
    cursor c2 is
    SELECT * FROM Tbl_Entity_Tmp01;-- ORDER BY AUTOID;

    /*
    rec_c1 c1%rowtype ;
    rec_c2 c2%rowtype ; */

    l_per number;
    v_start number;
    v_finish number;
    v_int number;
    v_tmp_str varchar2(1000);
    v_newVal_str varchar2(1000);
    v_word_str varchar2(1000);
    v_main_str varchar2(1000);
    v_counter number;

    Begin

    for rec_c1 IN C1
    loop

    for rec_c2 IN C2
    LOOP

    UPDATE TBL_ENTITY_TMP01
    SET NewEntityID = (SELECT rec_c1.Code2 FROM Tbl_Matching_Tmp01
    WHERE trim(rec_c2.REC_DT) = Trim(rec_c1.REC_DT1) AND
    rec_c1.MATCHINGPER >= 96)
    where NEWENTITYID = (SELECT NEWENTITYID FROM TBL_ENTITY_TMP01 WHERE trim(rec_c2.REC_DT) = Trim(rec_c1.REC_DT1) AND
    rec_c1.MATCHINGPER >= 96);

    commit;

    end loop;
    --close c2;

    end loop;

    --close c1;

    end;
    /

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    since we don't have your tables or data, we can not run test, or improve posted code.

    You report "it is not working" which is likely 100% true; 100% devoid of any actionable detail

    We don't know what results you got.
    We don't know what the results should be.
    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
    Feb 2012
    Posts
    36
    In the above procedure i want to

    Update My table Tbl_Entity_Tmp01 column 'NewEntityID' With another table Tbl_Matching_Tmp01 column code2 data based on the criteria

    Tbl_Entity_Tmp01.REC_DT = Tbl_Matching_Tmp01.REC_DT1
    AND
    Tbl_Matching_Tmp01.VMatchingPer >= 96%.

    Im getting the error like 'single-row subquery returns more than one row'.

    Im also trying to use merge statement to update, but still no luck :-(

    Here is the statement:

    merge into TBL_ENTITY_TMP01 using Tbl_Matching_Tmp01 on (Trim(Tbl_Matching_Tmp01.REC_DT1)=trim(TBL_ENTITY_ TMP01.REC_DT) ) when matched then
    update set newentityid = Tbl_Matching_Tmp01.CODE2

    here i get the error 'unable to get a stable set of rows in the source tables'


    Thanks for ur reply adacent

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Im getting the error like 'single-row subquery returns more than one row'.

    occurs when the topmost SELECT returns more than 1 row
    Code:
    UPDATE tbl_entity_tmp01 
    SET    newentityid = (SELECT rec_c1.code2 
                          FROM   tbl_matching_tmp01 
                          WHERE  Trim(rec_c2.rec_dt) = Trim(rec_c1.rec_dt1) 
                                 AND rec_c1.matchingper >= 96) 
    WHERE  newentityid = (SELECT newentityid 
                          FROM   tbl_entity_tmp01 
                          WHERE  Trim(rec_c2.rec_dt) = Trim(rec_c1.rec_dt1) 
                                 AND rec_c1.matchingper >= 96);
    Realize that we don't know exactly what you have & what the desired results should be
    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
    Feb 2012
    Posts
    36
    Thank you for your reply

Posting Permissions

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