UPDATE oeb_file
SET
oeb24=(select ogb12 from (select sum(ogb12) as ogb12,ogb31,ogb04 from ogb_file where ogb01='3T4-410001' group by ogb31,ogb04)
a where a.ogb31=oeb01
and a.ogb04=oeb04)
SET
oeb24=(select ogb12 from (select sum(ogb12) as ogb12,ogb31,ogb04 from ogb_file where ogb01='3T4-410001' group by ogb31,ogb04)
a where a.ogb31=oeb01
and a.ogb04=oeb04)
SET oeb24 = (select ogb12 from (select sum(ogb12) as ogb12,ogb31,ogb04 from ogb_file where ogb01='3T4-410001' group by ogb31,ogb04)
a where a.ogb31=oeb01
and a.ogb04=oeb04)
where exists (select 1 from (select sum(ogb12) as ogb12,ogb31,ogb04 from ogb_file where ogb01='3T4-410001' group by ogb31,ogb04)
b where b.ogb31=oeb01
and b.ogb04=oeb04) ;加一个update验证就好了,这是因为要更新的值是null引起的
SET
oeb24=nvl((select ogb12 from (select sum(ogb12) as ogb12,ogb31,ogb04 from ogb_file where ogb01='3T4-410001' group by ogb31,ogb04)
a where a.ogb31=oeb01
and a.ogb04=oeb04), oeb24)
再加个NVL吧 避免有NULL的 不可以SUM 否则结果还是NULL
SET
oeb24=(select [code=SQL]nvl(ogb12,0) from (select sum(ogb12) as ogb12,ogb31,ogb04 from ogb_file where ogb01='3T4-410001' group by ogb31,ogb04)
a where a.ogb31=oeb01
and a.ogb04=oeb04)[/code]
--ogb12是实际出货数量,为空的可以0来填充数据,所以用nvl来实现!
是oeb24字段不能为null吧,有这个约束存在,所以当你子查询里面为null没有数据的时候,就会报约束错误的。可以这样改下:
UPDATE oeb_file
SET oeb24 = nvl (
(select ogb12
from (select sum(ogb12) as ogb12, ogb31, ogb04
from ogb_file
where ogb01 = '3T4-410001'
group by ogb31, ogb04) a
where a.ogb31 = oeb01
and a.ogb04 = oeb04
)
,
' ');