Oracle中不能用UPDATE FROM的解决方法

(此原文在我的百度空间里，原样搬运到新浪博客)

又是oracle!!

今天的业务需求是在SQL语句中完成一个更新，但所要的参数需要在另外2张表中查询，而且肯定不能抛到JAVA层用多次查询来完成。

最后的结果是：首先要连用2次 LEFT JOIN 构造一张表，然后做为子查询放到UPDATE语句中作为筛选条件。

要是SQL SERVER数据库，直接用update .....from.(子查询) on ....这样的语句就可以了。

今天鼓捣了半天，一直出不来，搜索了下，我X，原来ORACLE不支持 UPDATE.... FROM

 

搜索到了2种解决方案。其中一种语句结构比较简单，以下是原文引用：

“

update (select a.sal asal,b.sal bsal,a.comm acomm,b.comm bcomm from emp a,emp1 b where a.empno = b.empno)
set asal = bsal,
acomm = bcomm;

这里的表是一个类视图。
当然你执行时可能会遇到如下错误:

ERROR 位于第 2 行:
ORA-01779: 无法修改与非键值保存表对应的列
这是因为新建的表emp1还没有主键的缘故

下面增加一个主键

alter table emp1
add constraint pk_emp1 primary key (empno);

执行之后

在执行前面的语句就能成功。

”

试了一会，果然行不通。我这次的情况是把子查询做为筛选，这个是把另一张实体表做为筛选条件。还是老实用另一种看上去麻烦的语句吧。

语句结构是这样的：

Update emp
Set(sal,comm) = (select sal,comm. From emp1 where emp.empno = emp1.empno)
Where exists (select 1 from emp1 where emp1.empno = emp.empno)

最后那句Where exists (select 1 from emp1 where emp1.empno = emp.empno)完全没见过，管他的，试试呗。

最后成功的语句是：

UPDATE CUSTOMER.CONTRACT_ITEM_DETAIL CID
SET(M3_USEABLE,M3_USED) = (select TE.M3_USEABLE,TE.M3_USED FROM (
select PU.ORDER_ID,C.CONTRACT_ID CONTRACT_ID,C.MATKL MATKL,C.MAKTX,C.M3 M3,C.M3_USEABLE+OI.NUM M3_USEABLE,C.M3_USED-OI.NUM M3_USED,
        OI.NUM NUM FROM CUSTOMER.CONTRACT_ITEM_DETAIL c
                 LEFT JOIN CUSTOMER.PURCHASE_ORDER pu
                  ON PU.CONTRACT_ID = C.CONTRACT_NO
                 LEFT JOIN CUSTOMER.ORDER_ITEM OI
                  ON PU.ORDER_ID = OI.ORDER_ID AND OI.ORDER_ITEM_SAP_NO = C.MATKL 
    WHERE PU.ORDER_ID = 2321)TE WHERE CID.CONTRACT_ID = TE.CONTRACT_ID AND CID.MATKL = TE.MATKL)
Where exists (select 1 from (
select PU.ORDER_ID,C.CONTRACT_ID CONTRACT_ID,C.MATKL MATKL,C.MAKTX,C.M3 M3,C.M3_USEABLE+OI.NUM M3_USEABLE,C.M3_USED-OI.NUM M3_USED,
        OI.NUM NUM FROM CUSTOMER.CONTRACT_ITEM_DETAIL c
                 LEFT JOIN CUSTOMER.PURCHASE_ORDER pu
                  ON PU.CONTRACT_ID = C.CONTRACT_NO
                 LEFT JOIN CUSTOMER.ORDER_ITEM OI
                  ON PU.ORDER_ID = OI.ORDER_ID AND OI.ORDER_ITEM_SAP_NO = C.MATKL 
    WHERE PU.ORDER_ID = 2321)TE WHERE TE.CONTRACT_ID = CID.CONTRACT_ID AND TE.MATKL = CID.MATKL)

 

其中子查询TE表整个代码写了2次，使看上去比较复杂，其实很简单。。最后这个更新语句只需要输入订单号ORDER_ID 和物料号MATKL，以及输入月份在JAVA中把月份数字拼到M后面就可以了。放在这里做个保存吧，以后要是有需要可以查阅。