sql - 根据特定条件使用另一个表的行更新一个表的行

Question

我要更新OFFICE_ID,OFFICE_TYPE的 FA_SUBLEDGER_MST表，通过使用 OFFICE_ID,OFFICE_TYPE来自 EMPLOYEE_MST基于以下条件的表:

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'.

我试过这个查询:

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'
  );

还有这个:

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'
  ) ;

但是两者都导致了这个错误:

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

我也试过:

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';

这导致了一个错误:

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.

这两个表都包含超过 100,000 条记录和 OFFICE_ID可以是1,2,3,4,5和 OFFICE_TYPE可以是'DE','DI','HO' , 因此他们经常重复。

我需要 Oracle 查询才能这样做。无法使用程序。

感谢您的帮助。

Answer 1

试试这个:

 WHERE (FA_SUBLEDGER_MST.OFFICE_ID != EMPLOYEE_MST.OFFICE_ID AND
        FA_SUBLEDGER_MST.OFFICE_TYPE !=EMPLOYEE_MST.OFFICE_TYPE) --AND will be converted to OR by SQL Engine
  AND FA_SUBLEDGER_MST.SL_CODE      = EMPLOYEE_MST.SL_CODE
  AND EMPLOYEE_MST.OFFICE_TYPE!     ='DHB'
  AND sl.STATUS = 'A'