0

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.

0

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

6
  • Sure, you can use WHEN NOT MATCHED THEN INSERT syntax, see: docs.oracle.com/cd/E11882_01/server.112/e41084/…
    – sjk
    Oct 7 '15 at 13:47
  • @ypercube: Thanks but. The above query is giving this error: Error at Command Line : 7 Column : 5 Error report - SQL Error: ORA-00904: "SL"."SL_CODE": invalid identifier 00904. 00000 - "%s: invalid identifier" The Table FA_SUBLEDGER_MST definitely contains SL_CODE. Oct 8 '15 at 5:42
  • And SL_CODE is Primary Key in FA_SUBLEDGER_MST and Foreign Key in EMPLOYEE_MST. Oct 8 '15 at 5:46
  • Changed sl -> s1, should fix ORA-00904. If SL_CODE is foreign key in EMPLOYEE_MST, you must make sure that the subquery returns one and only one row for each SL_CODE
    – sjk
    Oct 8 '15 at 5:57
  • @sjk: Thanks, It was stupid of me to notice that. It worked on 2 of my databases, but didn't work on 3rd Database. I think there could be some errors in records contained in that database. It gave error as: Error report - SQL Error: ORA-30926: unable to get a stable set of rows in the source tables 30926. 00000 - "unable to get a stable set of rows in the source tables" *Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause. *Action: Remove any non-deterministic where clauses and reissue the dml. 1 row merged. Oct 8 '15 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.