Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Md.Arif.

Asked: May 04, 2016 - 8:51 am UTC

Last updated: July 25, 2025 - 12:58 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Dear Tom,
I have one in my DB and some like below
Name Nmum
-----------
A 1
B 2
C 3
D 4
E 5

but now I wanted to change the C from Name column to .5 and which is re order the table data as below

Name Nmum
-----------
C 1
A 2
B 3
D 4
E 5

now can I do this. please help me.

and Chris said...

So you're first setting the value of Nmum to 0.5 for row C, then you want to renumber all rows starting from 1?

If so, here's one way to do this in SQL:

create table t (
  name varchar2(1), num number
);

insert into t values ('A', 1);
insert into t values ('B', 2);
insert into t values ('C', 3);
insert into t values ('D', 4);
insert into t values ('E', 5);

update t set num = 0.5
where  name = 'C';

update t
set    num = (
         select n from (
           select name, 
                  row_number() over (order by num) n 
           from   t
         ) s
         where s.name = t.name
       );
       
select * from t
order  by num;

N        NUM
- ----------
C          1
A          2
B          3
D          4
E          5

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Md.Arif Hossain, May 04, 2016 - 9:49 am UTC

Thanks a lot
Connor McDonald
May 04, 2016 - 11:35 am UTC

Glad we could help

Re order in application

Navneel Premlal Maharaj, July 24, 2025 - 2:22 pm UTC

Now, how can I implement this in an application where I have a Group of Transaction , with associated transactions that have and order. But the user want to update the order of associated transactions in various grouped transactions over time. To retain order and reorder as needed.

Chris Saxon
July 25, 2025 - 12:58 pm UTC

I don't understand what you mean - please clarify
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.