Reverse row "order" value from table MySQL with PHP

Nick Published at Dev

nick

I have a table with rows and each one has an "order" value. Now, I have like 500 rows and I need to reverse the "order" values. So the first one should be last and so on.

For example:

Name   |  Order
-------------------
Row 1  |    1
Row 2  |    4
Row 3  |    5
Row 4  |    8
Row 5  |    12
Row 6  |    20

Should now be:

Name   |  Order
-------------------
Row 1  |    20
Row 2  |    12
Row 3  |    8
Row 4  |    5
Row 5  |    4
Row 6  |    1

I need to do this just one time so performance doesn't really matter. How can I do this using PHP?

Paul Spiegel

If your only requirement is

So the first one should be last and so on

you can just negate the values (as I wrote in the comments). That can be done with

update mytable set `Order` = - `Order`

However - Here is an SQL script which will "swap" the values in the Order column from least to greatest, from second least to second greatest ond so on..

create table tmp_asc(
  i int auto_increment primary key,
  o int,
  index (o)
);

insert into tmp_asc(o)
  select `Order` from mytable order by `Order` asc;

create table tmp_desc(
  i int auto_increment primary key,
  o int,
  index (o)
);

insert into tmp_desc(o)
  select `Order` from mytable order by `Order` desc;

update mytable t
join tmp_asc on tmp_asc.o = t.Order
join tmp_desc on tmp_desc.i = tmp_asc.i
set t.Order = tmp_desc.o;

drop table tmp_asc, tmp_desc;

Demo

MySQL 8 with window functions makes it simpler:

with cte as (
  select `Order`
  , row_number() over (order by `Order` asc)  as rn_asc
  , row_number() over (order by `Order` desc) as rn_desc
  from mytable
)
update mytable t
join cte a on a.Order = t.Order
join cte d on d.rn_desc = a.rn_asc
set t.Order = d.Order;

Demo

Collected from the Internet

Please contact [email protected] to delete if infringement.