update registry_info set student_id = (select to_char(site_id)||to_char(rownum) from (select * from registry_info order by result_sum desc));不晓得是不是这个意思?
不知道这样行不行? update registry_info ri set student_id=(select site_id||rownum from (select * from registry_info order by result_sum) where student_id=ri.student_id and rownum=1);
sorry,上面的语句无法执行表里没有主键,不能用一句sql执行
update registry_info set student_id=(select sid from (select site_id,site_id||rownum sid from (select * from registry_info order by result_sum desc)) where site_id=registry_info.site_id and rownum=1)
查考一下 update name 2 set columnName = ( select value 3 from lookup 4 where lookup.keyname = name.keyname 5 and otherColumn = :other_value ) 6 where exists ( select value 7 from lookup 8 where lookup.keyname = name.keyname 9 and otherColumn = :other_value ) 10 /
update registry_info set student_id=site_id||rank() over(order by result_sum desc)
update registry_info ri set student_id=(select site_id||rownum from (select * from registry_info order by result_sum) where student_id=ri.student_id and rownum=1);
2 set columnName = ( select value
3 from lookup
4 where lookup.keyname = name.keyname
5 and otherColumn = :other_value )
6 where exists ( select value
7 from lookup
8 where lookup.keyname = name.keyname
9 and otherColumn = :other_value )
10 /