Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2010
    Posts
    2

    Unanswered: Trouble update mulitple rows from one table to another in ORACLE

    In MySQL this seems easy. I have not found a way to do this in Oracle without using PL/SQL to iterate through each returned row. here is my query.

    I basically want to copy/migrate all the values from one field in one table to another field in another table...
    the unique identifier is member_id.
    Update member_profile
    set ratings_team =
    (select departments3 from mb_company_info ci
    where member_profile.member_id = ci.member_id)

    Single row subquery returns more than one row error

    I have tried many combinations of SQL..

    Can anyone help me?

    PS this also didnt work:
    Update (Select mp.ratings_team, ci.departments3 from member_profile mp, mb_company_info ci where mp.member_id = ci.member_id and ci.order_id =0 and departments3 is not null)
    set ratings_team = departments3 ;

    SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
    01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
    *Cause: An attempt was made to insert or update columns of a join view which
    map to a non-key-preserved table.
    *Action: Modify the underlying base tables directly.

    Both tables already have primary keys which I am joining on
    Last edited by victor1sbr; 03-22-10 at 14:51.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by victor1sbr View Post
    Can anyone help me?
    Just look a the error message, it tells you the reason.


    Apparently the join between member_profile and mb_company_info returns more than one row for a given member_id. Which of them should Oracle choose?
    If MySQL actually runs without errors with the same statement, that either means your data in MySQL is different (e.g. only a single row per member_id in mb_company_info) or it's a bug in MySQL where it's simply taking some random value from the mutliple rows being returned.

    If your mb_company_info indeed contains more than one entry for a single member_id you have to investigate why that happens because apparently you don't expect that.

    A workaround would be to simply apply an aggregate on the sub-select which ensures that only a single value will be returned:

    Code:
    UPDATE member_profile
       SET ratings_team =
           (SELECT max(departments3)
              FROM mb_company_info ci
             WHERE member_profile.member_id = ci.member_id)

  3. #3
    Join Date
    Mar 2010
    Posts
    2

    Wink You're right.

    i looked more closely at my test queries and there were multiple duplicate member_ids being
    returned.. I was able to get only distinct values via a modification to my query..
    thanks for your help!!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I wonder how this

    Quote Originally Posted by victor1sbr View Post
    there were multiple duplicate member_ids
    can coexist with

    Quote Originally Posted by victor1sbr View Post
    unique identifier is member_id
    and

    Quote Originally Posted by victor1sbr View Post
    ables already have primary keys which I am joining on
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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