8 Replies Latest reply: Sep 4, 2014 1:07 PM by Biju Das RSS

    How to update a column from another table




      We have some vendors which are set as employees but without employee_id for some reason.  So I like to update those records with correct employee_id from a HR view.  Here is what I tried:


      update po.po_vendors x

      set employee_id = (select person_id from apps.per_people_v7 y

                 where y.first_name||' '||y.last_name = x.vendor_name)


      Got error: single row subquery returns more than one row.


      If I use this statement to check, I got correct employee_id or person_id back:


      select person_id from apps.per_people_v7 y, po.po_vendors x

                 where y.first_name||' '||y.last_name = x.vendor_name


      When I checked/compared with some old posts here, I just couldn't see where I made mistake.  Please advise.