某程序中每次修改都需要遍历对应条件的记录进行批量更新
如下sql语句:
update mytable set ksbh=(select '20100203'||ltrim(to_char(rownum,'00')) from mytable
where to_char(kssj,'yyyy-mm-dd')='2010-02-03' order by kssj asc)
where to_char(kssj,'yyyy-mm-dd')='2010-02-03'
当然这是错误的sql语句,我想实现的是更新mytable表中日期为2010-02-03的记录的ksbh字段的值为:
年月日+2位的序号 我用了rownum按kssj(日期字段)正序排序,这条sql语句如何写?请指点迷津
如下sql语句:
update mytable set ksbh=(select '20100203'||ltrim(to_char(rownum,'00')) from mytable
where to_char(kssj,'yyyy-mm-dd')='2010-02-03' order by kssj asc)
where to_char(kssj,'yyyy-mm-dd')='2010-02-03'
当然这是错误的sql语句,我想实现的是更新mytable表中日期为2010-02-03的记录的ksbh字段的值为:
年月日+2位的序号 我用了rownum按kssj(日期字段)正序排序,这条sql语句如何写?请指点迷津
is
begin
for i in 0..99 loop
update mytable
set ksbh=(
select '20100203'||lpad(i, 2, 0)
from dual
)
where to_char(kssj,'yyyy-mm-dd')='2010-02-03' order by kssj asc;
end loop;
end;
create or replace procedure p_mytable_update
is
begin
for i in 0..99 loop
update mytable
set ksbh=(
select '20100203'||lpad(i, 2, 0)
from dual
)
where to_char(kssj,'yyyy-mm-dd')='2010-02-03' order by kssj asc;
end loop;
commit;
end;
update mytable a
set ksbh =
(select rn
from (select rwd,
to_char(kssj, 'yyyymmdd') ||
lpad(to_char(rownum), 2, '0') rn
from (select rowid rwd, t.*
from mytable t
where to_char(kssj, 'yyyy-mm-dd') = '2010-02-03'
order by kssj)) b
where b.rwd = a.rowid)
where to_char(kssj, 'yyyy-mm-dd') = '2010-02-03'