CODEWITHSUNDEEP
mysql
Elizabeth Stout
Elizabeth Stout

Reputation: 23

How to have my Database reorder id number in order using MySQL?

I have a player database where the Player ID is not sequential:

1 Player 1

2 Player 2

16 Player 16

27 Player 27

28 Player 28

I would like to maintain the order of the players, but change their IDs so that they become sequential (i.e. go up in increments of 1).

Can this be achieved using a SQL statement?

Desired result:

1 Player 1

2 Player 2

3 Player 16

4 Player 27

5 Player 28

I have a 28,000 line Database that 25,000 or so are going to be deleted and I want to move the remaining 3000 down in order.

Thanks for the help in advance and I hope I explained that well enough.

Upvotes: 2

Views: 1053

Answers (2)

Pevara
Pevara

Reputation: 14308

if it is just a simple on off you are after you could run something like this on your database:

update players as p 
set p.id = (
    SELECT count(*) from (select id from players) as sub 
    where p.id > sub.id
)

or use >= if you want to start at 1

Upvotes: 0

Obsidian Age
Obsidian Age

Reputation: 42374

Assuming your gaps are in the ID field of your table, you don't need to worry about the holes at all. The ID column should be completely meaningless, and used purely to tie the values from the other columns together. It's quite common to have gaps in the ID values, and doesn't impact your table at all (aside from aesthetics).

For example, you have a third column there that is equal to your ID. I assume that is the actual data you care about (such as a player tag). When running a SELECT for this player, you would run your query based off of that column. A player with a player_id of 2 can have an id of 1 - that's totally fine!

For example, if you wanted to return this player, you would run:

SELECT * FROM players WHERE player_id = 2;

Which might return:

ID  | Name   |  player_id
-------------------------
1   | player |  2

The ID of 1 in the above doesn't matter, because you already have all the data you care about. It's simply an index.

You say you're going to delete 3000 or so rows, and that's still completely fine even without an 'order' per se. Simply use a WHERE clause to delete based off of one of the other columns in the table. This will cherry-pick out the target rows to delete (leaving further gaps).

Hope this helps!

Upvotes: 2

Related Questions