我想更新一个表的字段，可是写完报ora-00936缺少表达式，代码贴出来，高手帮着看看，小生感激不尽
update GATHER_STATISTICS t
set BATCH_SUCCESS_COUNT = select decode(cnt, 0,0) as cnt
from ((SELECT count(g.WARNING_TYPE) as cnt
from jk_gather_info_history g
where g.WARNING_TYPE = 0
and g.batch_id = t.batch_id
and t.userid = 2
and g.gather_date >=
to_date('2009-4-7 16:24:24',
'yyyy-mm-dd HH24:MI:SS')
and g.gather_date <=
to_date('2009-4-7 16:24:27',
'yyyy-mm-dd HH24:MI:SS')
group by t.batch_id))
[b]问题补充：[/b]
感谢lovewhzlq的回答，但是还是不行，还是报同样的错误
update GATHER_STATISTICS t
set BATCH_SUCCESS_COUNT = select decode([color=red]c.cnt[/color], 0,0) as cnt
from ((SELECT count(g.WARNING_TYPE) as cnt
from jk_gather_info_history g
where g.WARNING_TYPE = 0
and g.batch_id = t.batch_id
and t.userid = 2
and g.gather_date >=
to_date('2009-4-7 16:24:24',
'yyyy-mm-dd HH24:MI:SS')
and g.gather_date <=
to_date('2009-4-7 16:24:27',
'yyyy-mm-dd HH24:MI:SS')
group by t.batch_id))[color=red] c[/color]
这样试试
[code="java"]
update GATHER_STATISTICS t
set BATCH_SUCCESS_COUNT = select decode(c.cnt, 0,0) as cnt
from ((SELECT count(g.WARNING_TYPE) as cnt
from jk_gather_info_history g
where g.WARNING_TYPE = 0
and g.batch_id = t.batch_id
and t.userid = 2
and g.gather_date >=
to_date('2009-4-7 16:24:24',
'yyyy-mm-dd HH24:MI:SS')
and g.gather_date <=
to_date('2009-4-7 16:24:27',
'yyyy-mm-dd HH24:MI:SS')
group by t.batch_id))
[/code]
看了下Lz的问题 看下下面的Sql能运行不
update GATHER_STATISTICS t
set BATCH_SUCCESS_COUNT =
select decode(cnt, 0, 0)
from ((SELECT count(g.WARNING_TYPE) cnt
from jk_gather_info_history g, GATHER_STATISTICS m
where g.WARNING_TYPE = 0 and g.batch_id = m.batch_id and
m.userid = 2 and
g.gather_date >=
to_date('2009-4-7 16:24:24', 'yyyy-mm-dd HH24:MI:SS') and
g.gather_date <=
to_date('2009-4-7 16:24:27', 'yyyy-mm-dd HH24:MI:SS')
group by m.batch_id)) a
:wink: :wink:
不好意思上面可能还有点小问题 改成
update GATHER_STATISTICS t
set BATCH_SUCCESS_COUNT =
select decode([color=red]a[/color].cnt, 0, 0)
from ((SELECT count(g.WARNING_TYPE) cnt
from jk_gather_info_history g, GATHER_STATISTICS m
where g.WARNING_TYPE = 0 and g.batch_id = m.batch_id and
m.userid = 2 and
g.gather_date >=
to_date('2009-4-7 16:24:24', 'yyyy-mm-dd HH24:MI:SS') and
g.gather_date <=
to_date('2009-4-7 16:24:27', 'yyyy-mm-dd HH24:MI:SS')
group by m.batch_id)) [color=red]a[/color]
LZ再看看
朋友，问题要自动关闭啦，结分哦