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

    How to update a column from another table

    user11209099

      Hello,

       

      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.

       

      Thanks,

      Ning