How to sort values in columns and update table?

You would have to use a second table

create a new table games2 with the same structure as your games table, making sure the ID is auto-incrementing
```
CREATE TABLE `games2` LIKE `games`;
```

copy the data, sorted, into games2

INSERT INTO `games2` (`Name`, `SomeDescription`) SELECT `Name`, `SomeDescription` FROM `games` ORDER BY `Name`

drop or move the old table

-- DROP TABLE `games`;
-- or
RENAME TABLE `games` TO `games1`;

These steps will result in what you want.

You can use the ROW_NUMBER ranking function to renumber the rows.

SELECT UnsortedId = id
, SortedId = ROW_NUMBER() OVER (ORDER BY g.name, g.id)
FROM games

You can use alter table command which is simpler.

mysql> ALTER TABLE games ORDER BY name asc;

That's all !

it is simple. just use a few codes. I have tried this and it is working. first create a temporary table while sorting values at the same time.

    create table new as select * from games order by name;

and then drop the games table using,

    drop table games;

now create games table again with same properties and data as in new (where sorting here is an optional) using,

    create table games as select * from new order by name;

now drop the temp table 'new'

    drop table new;

now check your table. it must be sorted out.

Related