a b c
1 A
2 A
3 A
4 A
5 A
6 A
7 B
8 B一个批量更新的sqla b c 三个字段 想把 所有 b='A' 的集合前两条置成 x,3-4条置成y,5,6条置成z
update table set c='x' where (?)
update table set c='y' where (?)update table set c='z' where (?)将这三条做成事务一起提交,问题关键就是 怎样把取出的集合分段,rownum好像不可以
1 A
2 A
3 A
4 A
5 A
6 A
7 B
8 B一个批量更新的sqla b c 三个字段 想把 所有 b='A' 的集合前两条置成 x,3-4条置成y,5,6条置成z
update table set c='x' where (?)
update table set c='y' where (?)update table set c='z' where (?)将这三条做成事务一起提交,问题关键就是 怎样把取出的集合分段,rownum好像不可以
select t.a,from (
select row_number() over(patition by b order by a) row_number,a
) t
where t <3
set c = (case trunc(a / 2.5) when 0 then 'x' when 1 then 'y' when 2 then 'z' end)
where b = 'a';更新后结果如下:
1 a x
2 a x
3 a y
4 a y
5 a z
6 a z
7 b
8 b
9 b
就是 b='A'的集合 前N条 X 值 ,2N条 Y值,3N条 Z值
结合merge操作实现就可以了!ps:还是投机取巧了
按道理应该有办法把 前两条置成一类,3-4条置成一类5,6条置成一类!
set c=(select b.c from
(select a,case when rn between 1 and 2 then 'x'
when rn between 3 and 4 then 'y'
when rn between 5 and 6 then 'z' end c
from (select a,rownum rn from a order by a)) b
where a.a=b.a )
where b='a'
where b='A' and c is null and rownum<=2;
update table set c='y'
where b='A' and c is null and rownum<=2;
update table set c='z'
where b='A' and c is null and rownum<=2;我想这样是最简单的