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)
解决方案 »
- 急!~远程访问服务器问题
- 请问oracle 10g中彻底删除视图,用purge怎么报错?
- select * from user_all_tables sample(10) 大家来看看
- 斑竹:pro*c算oracle的高级技术吗?oracle的高级技术有哪些,最好分类一下
- oracle自定义函数转换为sql server函数的软件,方法
- 100分 求sql语句!急急急!!!!!
- [Ora9i]请问Index有什么作用?在编程中需要注意什么?
- Oracle一般是裝在什麼OS下??請高手指點,謝謝!!
- 急:我的OracleOraHome81TNSListener启动不了,我试了csdn上的这么多办法都不行呀?
- java连接oracle数据库报错ntly know of service requested in connect descriptor
- oracle for update
- 怎样调用在oracle客户端写的函数????
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
)
,
' ');