根据特定条件使用另一个表的行更新一个表的行
我想更新OFFICE_ID,OFFICE_TYPE of FA_SUBLEDGER_MST表，方法是根据以下条件从EMPLOYEE_MST表中使用OFFICE_ID,OFFICE_TYPE：
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错误: ORA-01427:单行子查询返回多行。 01427。00000 -“单行子查询返回多行。
我也试过：
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错误: ORA-01779:无法修改映射到非密钥保存表的列。 01779。00000 -“无法修改映射到非密钥保存表的列”。 *原因:试图插入或更新连接视图的列，这些列映射到非密钥保存的表。 *操作:直接修改基础基表。
这两个表都包含超过10万条记录，OFFICE_ID可以是1,2,3,4,5，OFFICE_TYPE可以是'DE','DI','HO'，因此它们经常重复。
为此，我需要一个Oracle查询。不能用手术。
会感谢你的帮助。
回答 4
Stack Overflow用户
发布于 2015-10-08 07:23:06
终于找到了一个解决方案：
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)
;原始线程：Updating row of one table using rows of another table based on specific conditions
Stack Overflow用户
发布于 2015-10-07 07:21:13
试试这个：
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' Stack Overflow用户
发布于 2015-10-07 07:04:52
试一试
UPDATE FA_SUBLEDGER_MST
SET sl.OFFICE_ID = em.OFFICE_ID
,sl.OFFICE_TYPE = em.OFFICE_TYPE
FROM FA_SUBLEDGER_MST sl
INNER JOIN EMPLOYEE_MST em 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'https://stackoverflow.com/questions/32985514
复制相似问题
腾讯云开发者
