我要更新一个表的
update wg_zb_yw set 一至四月发展=(select count(case when
a.create_date > = to_date ('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_date < = to_date ('2007-04-30 23:59:59','yyyy-mm-dd hh24:mi:ss') then b.user_id end)),
本月发展=(select count(case when
a.create_date > = to_date ('2007-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_date < = to_date ('2007-05-31 23:59:59','yyyy-mm-dd hh24:mi:ss') then b.user_id end)),
本周发展=
(select count(case when
a.create_date > = to_date ('2007-05-26 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_date < = to_date ('2007-05-31 23:59:59','yyyy-mm-dd hh24:mi:ss') then b.user_id end))
from v_tba_ask_list a,v_tcm_user b
where a.sequ = '0' and (a.serv_no like '1%' or a.serv_no = '4J128' or a.serv_no = '4J129')
and b.prod_no in('JP1003','JP1011')
and b.prod_type in('JP1003-011','JP1003-012','JP1011-008','JP1011-009','JP1003-004','JP1011-002')
and a.num = b.num AND A.STATE <> '6'
and b.user_seq = '0' and b.state = '1' and b.user_state = '1'
and b.prod_type in('JP1003-004','JP1011-002)为何我只能一个字段一个字段更新就不会报错,如果三个字段同时更新 就会在更新的第二个字段前报 “未找到预期FROM ”.谢谢大家了 在线等
update wg_zb_yw set 一至四月发展=(select count(case when
a.create_date > = to_date ('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_date < = to_date ('2007-04-30 23:59:59','yyyy-mm-dd hh24:mi:ss') then b.user_id end)),
本月发展=(select count(case when
a.create_date > = to_date ('2007-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_date < = to_date ('2007-05-31 23:59:59','yyyy-mm-dd hh24:mi:ss') then b.user_id end)),
本周发展=
(select count(case when
a.create_date > = to_date ('2007-05-26 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_date < = to_date ('2007-05-31 23:59:59','yyyy-mm-dd hh24:mi:ss') then b.user_id end))
from v_tba_ask_list a,v_tcm_user b
where a.sequ = '0' and (a.serv_no like '1%' or a.serv_no = '4J128' or a.serv_no = '4J129')
and b.prod_no in('JP1003','JP1011')
and b.prod_type in('JP1003-011','JP1003-012','JP1011-008','JP1011-009','JP1003-004','JP1011-002')
and a.num = b.num AND A.STATE <> '6'
and b.user_seq = '0' and b.state = '1' and b.user_state = '1'
and b.prod_type in('JP1003-004','JP1011-002)为何我只能一个字段一个字段更新就不会报错,如果三个字段同时更新 就会在更新的第二个字段前报 “未找到预期FROM ”.谢谢大家了 在线等
一至四月发展=(select count(case when
a.create_date > = to_date ('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_date < = to_date ('2007-04-30 23:59:59','yyyy-mm-dd hh24:mi:ss') then b.user_id end)),
你这样写的话,都要写from 如果你条件都一样的话,象下面那样写
update tabel
set (字段1,字段2...)=
(select 字段1,字段2... from tabel2)//必须是字查询
update tabel
set (字段1,字段2...)=
(select 字段1,字段2... from tabel2)//必须是字查询
试了一下,当select 字段1,字段2... from tabel2只返回一条记录时,可以实现。看楼主的意思,用count()应该没有问题。
一起学习:)
SET (一至四月发展, 本月发展, 本周发展) =
(SELECT
SUM(CASE WHEN a.create_date > = to_date('2007-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.create_date < = to_date('2007-04-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss') THEN 1 ELSE 0 END),
SUM(CASE WHEN a.create_date > = to_date('2007-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.create_date < = to_date('2007-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss') THEN 1 ELSE 0 END),
SUM(CASE WHEN a.create_date > = to_date('2007-05-26 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND a.create_date < = to_date('2007-05-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss') THEN 1 ELSE 0 END)
FROM v_tba_ask_list a, v_tcm_user b
WHERE a.sequ = '0'
AND (a.serv_no LIKE '1%' OR
a.serv_no = '4J128' OR
a.serv_no = '4J129')
AND b.prod_no IN
('JP1003', 'JP1011')
AND b.prod_type IN
('JP1003-011', 'JP1003-012',
'JP1011-008', 'JP1011-009',
'JP1003-004', 'JP1011-002')
AND a.num = b.num
AND a.state <> '6'
AND b.user_seq = '0'
AND b.state = '1'
AND b.user_state = '1'
AND b.prod_type IN
('JP1003-004', 'JP1011-002'))