Updating row of one table using rows of another table based on specific conditions

Question

I want to update OFFICE_ID,OFFICE_TYPE of FA_SUBLEDGER_MST table, by using OFFICE_ID,OFFICE_TYPE from EMPLOYEE_MST table based on following conditions:

EMPLOYEE_MST.SL_CODE = FA_SUBLEDGER_MST.SL_CODE
EMPLOYEE_MST.OFFICE_ID <> SL.OFFICE_ID
  OR EMPLOYEE_MST.OFFICE_TYPE <> SL.OFFICE_TYPE
AND EMPLOYEE_MST.OFFICE_TYPE != 'DHB'

I tried this query:

UPDATE FA_SUBLEDGER_MST sl
SET
  (sl.OFFICE_ID, sl.OFFICE_TYPE) =
  (SELECT 
       emp.OFFICE_ID, emp.OFFICE_TYPE
   FROM EMPLOYEE_MST emp
   WHERE emp.OFFICE_ID <> sl.OFFICE_ID
      OR emp.OFFICE_TYPE <> sl.OFFICE_TYPE
      AND sl.SL_CODE = emp.SL_CODE
      AND emp.OFFICE_TYPE != 'DHB')
WHERE sl.STATUS = 'A'
  AND EXISTS (SELECT 1
              FROM EMPLOYEE_MST emp
              WHERE emp.OFFICE_ID <> sl.OFFICE_ID
                 OR emp.OFFICE_TYPE <> sl.OFFICE_TYPE
                AND emp.SL_CODE = sl.SL_CODE  
                AND emp.OFFICE_TYPE != 'DHB');

But both gave this error:

SQL Error: ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"

And this:

UPDATE
  (SELECT em.OFFICE_ID emoffid,
    SL.OFFICE_ID sloffid,
    em.OFFICE_TYPE emofftype,
    SL.OFFICE_TYPE slemofftype,
    SL.STATUS
  FROM EMPLOYEE_MST em
  JOIN FA_SUBLEDGER_MST SL
  ON em.SL_CODE      =SL.SL_CODE
  WHERE em.OFFICE_ID<>SL.OFFICE_ID
  OR em.OFFICE_TYPE <>SL.OFFICE_TYPE
  AND em.OFFICE_TYPE!='DHB'
  AND SL.STATUS      ='A'
  ) t
SET t.sloffid   =t.emoffid
WHERE t.emoffid<>t.sloffid
OR t.emofftype <>t.slemofftype
AND t.emofftype!='DHB'
AND t.STATUS    ='A';

This returns those errors:

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.

And also this:

UPDATE FA_SUBLEDGER_MST
SET
  (
    OFFICE_ID,
    OFFICE_TYPE
  )
  =
  (SELECT OFFICE_ID,
    OFFICE_TYPE
  FROM EMPLOYEE_MST
  WHERE (FA_SUBLEDGER_MST.OFFICE_ID != EMPLOYEE_MST.OFFICE_ID
  OR FA_SUBLEDGER_MST.OFFICE_TYPE!  =EMPLOYEE_MST.OFFICE_TYPE)
  AND FA_SUBLEDGER_MST.SL_CODE      = EMPLOYEE_MST.SL_CODE
  AND EMPLOYEE_MST.OFFICE_TYPE!     ='DHB'
  ) ;

It worked but it updates all 124,451 rows, while the rows that met with the above specified condition are only 4,522 rows.

Both the tables contain more than 100,000 records and OFFICE_ID can be 1,2,3,4,5...18 and OFFICE_TYPE can be 'DE','DI','HO', Hence they repeat frequently.

I need a oracle query to do so. Can't use a procedure.

Would be thankful for your help.

Answer 1

You could try MERGE instead of UPDATE. It is often faster and (in my opinion) more clear than UPDATE when updating a table based on another table

merge into FA_SUBLEDGER_MST s1
using (
    select SL_CODE, OFFICE_ID, OFFICE_TYPE
    from EMPLOYEE_MST
    where OFFICE_TYPE != 'DHB'
) emp
on (s1.SL_CODE = emp.SL_CODE)
when matched then update set 
    s1.OFFICE_ID = emp.OFFICE_ID,
    s1.OFFICE_TYPE = emp.OFFICE_TYPE
where sl.STATUS = 'A'
    and (s1.OFFICE_ID <> emp.OFFICE_ID
    or  s1.OFFICE_TYPE <> emp.OFFICE_TYPE);

Make sure that using-subquery doesn't return duplicate rows for each SL_CODE

Also your last example is missing the STATUS = 'A' condition