求一条批量更新的sql( update ... select )

[无爱大叔]

某程序中每次修改都需要遍历对应条件的记录进行批量更新
如下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语句如何写？请指点迷津

[Phoenix_99]

用过程，因为2位序号是可变的，用循环来控制

[无爱大叔]

请问下过程要怎么写???????????

[碧水幽幽泉]

引用 2 楼 xhbmj 的回复:

请问下过程要怎么写???????????

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;
end;

[碧水幽幽泉]

忘记commit了,呵呵！

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;

[tangren]

--不用过程试一下
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'

[zhangwonderful]

引用 5 楼 tangren 的回复:

SQL code--不用过程试一下update mytable aset ksbh=
(select rnfrom (select rwd,
to_char(kssj,'yyyymmdd')||
lpad(to_char(rownum),2,'0') rnfrom (select rowid rwd, t.*from mytable twhere to_char(kssj,'yyyy-mm-dd')='2010-02-03'orderby kssj)) bwhere b.rwd= a.rowid)where to_char(kssj,'yyyy-mm-dd')='2010-02-03'

方法可行

[无爱大叔]

谢谢tangren的答案，结贴