Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2013
    Posts
    46

    Unanswered: Error while updating multiple rows

    Hi,

    I built an update query and need to update multiple rows. It's pretty complex but i have a feeling i am doing more than i need to for this logic to work. basically i need to update table a where ever substr of some column in table a = column in table b. These values may differ from row to row.

    UPDATE EMP a
    SET COMPANY=(select DEPT_ID from DEPT b
    WHERE SUBSTR(a.EMP_ID,0,19)=b.EMP_ID
    and b.DEPT_ID<12345)
    WHERE SUBSTR(a.EMP_ID,0,19)=(select b.EMP_ID from ACCOUNT_SFREP b where b.DEPT_ID<12345);

    ERROR CODE: ORA-01427

    Please help me fix my code if you understand what i'm trying to do. MERGE will not work as i will be using dynamic sql to generate this query using variables to execute this.

  2. #2
    Join Date
    Sep 2013
    Posts
    46
    btw, please - if you believe merge is possible, help me build a merge query..

  3. #3
    Join Date
    Sep 2013
    Posts
    46
    So, I came up with the following merge syntax, but it didn't work as expected =(

    MERGE INTO EMP a
    USING (select distinct x.DEPT_ID DEPT_ID, x.EMP_ID EMP_ID
    from DEPT x, EMP y
    WHERE SUBSTR(y.EMP_ID,0,19)=x.EMP_ID) b
    --and to_number(x.DEPT_ID)<12345) b
    ON (SUBSTR(a.EMP_ID,0,19)=b.EMP_ID)

    when matched then
    update set a.COMPANY=b.DEPT_ID;


    any suggestions welcome!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    we don't have your tables.
    we don't have your data.
    we don't know your requirements.

    I suggest that you learn how to ask better question.
    Let me google that for you
    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
    Dec 2013
    Posts
    14
    This query select b.EMP_ID from ACCOUNT_SFREP b where b.DEPT_ID<12345
    is returnign multiple rows cause of which your getting the error.
    Rather use in instead of =

  6. #6
    Join Date
    Sep 2013
    Posts
    46
    Quote Originally Posted by anacedent View Post
    we don't have your tables.
    we don't have you data.
    we don't have your requirements.
    Table EMP:
    COMPANY VARCHAR2(40); --THIS COLUMN IS NULL (needs to be updated with DEPT.DEPT_ID)
    EMP_ID VARCHAR2(40); --THIS COLUMN CONTAINS DATA WHICH NEEDS TO BE MATCHED USING SUBSTR(FIRST 19 CHARACTERS) WITH FULL VALUE OF DEPT.EMP_ID

    Table DEPT:
    DEPT_ID VARCHAR2(40); --THIS COLUMN CONTAINS ID'S WHICH NEED TO BE UPDATED INTO EMP TABLE FOR EACH ROW WHERE CONDITION IS MATCHED (SUBSTR(EMP.EMP_ID,0,19)=DEPT.EMP_ID)
    EMP_ID VARCHAR2(40);

    I hope this helps understand the requirement a little better.

    Thank you

  7. #7
    Join Date
    Sep 2013
    Posts
    46
    Tried doing the following, but got error code ORA-01779.

    UPDATE (select y.DEPT_ID, y.EMP_ID from DEPT x, EMP y
    WHERE SUBSTR(y.EMP_ID,0,19)=x.EMP_ID and x.DEPT_ID<>'12345')
    SET COMPANY=(select b.DEPT_ID from DEPT b, EMP a
    WHERE SUBSTR(a.EMP_ID,0,19)=b.EMP_ID and b.DEPT_ID<>'12345')
    Last edited by jigmod01; 12-13-13 at 04:27.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Maybe, instead of randomly choosing different approaches, what about sticking to the (most preferred) one?

    You highlighted MERGE, so it could be simplified to this (not tested as I have no tables):
    Code:
    MERGE INTO EMP a
      USING (select x.DEPT_ID DEPT_ID, x.EMP_ID EMP_ID from DEPT x where to_number(x.DEPT_ID)<12345) b
      ON (SUBSTR(a.EMP_ID,0,19)=b.EMP_ID)
    when matched then
      update set a.COMPANY=b.DEPT_ID;
    Of course this makes sense in DEPT.DEPT_ID is unique. Is it your case? If there are duplicates (shown by the query below), from which row in DEPT shall be taken DEPT_ID value?
    Code:
    select emp_id, count(*) no_of_duplicates
    from dept
    where to_number(DEPT_ID)<12345
    group by emp_id
    having count(*) > 1;
    Of course, primary key on DEPT.EMP_ID would ensure ther is (at most) one row with the new value in DEPT and also it would got rid of ORA-01779.

  9. #9
    Join Date
    Sep 2013
    Posts
    46
    Quote Originally Posted by flyboy View Post
    Maybe, instead of randomly choosing different approaches, what about sticking to the (most preferred) one?

    You highlighted MERGE, so it could be simplified to this (not tested as I have no tables):
    Code:
    MERGE INTO EMP a
      USING (select x.DEPT_ID DEPT_ID, x.EMP_ID EMP_ID from DEPT x where to_number(x.DEPT_ID)<12345) b
      ON (SUBSTR(a.EMP_ID,0,19)=b.EMP_ID)
    when matched then
      update set a.COMPANY=b.DEPT_ID;
    Of course this makes sense in DEPT.DEPT_ID is unique. Is it your case?
    Thank you Flyboy, your suggestions were helpful in successfully deploying my sp.

Posting Permissions

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