2

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.

Community
  • 1
  • 1
Elizabeth Stout
  • 23
  • 6

2 Answers2

1

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!

Obsidian Age
  • 41,205
  • 10
  • 48
  • 71
  • Thanks, I think on the other tables the ID field would be ok to leave holes in. But the Player ID Field is used on the game. You look for an ID and if there isn’t anything there, it pops up with “User not Found.” Before opening the game out of beta, we wanted to clear out the accounts that are dead and shift the auto increment down (Which I know how to do.) to clean up the numbers. Thanks for the fast reply! – Elizabeth Stout Feb 08 '18 at 22:13
  • Even gaps in `player_id` won't matter, because you're just running a search against a specific ID. Just like a search for `9999` probably wouldn't find anything, maybe a search for `17` wouldn't find anything. There's literally no need to do what you're asking other than aesthetics :) It's still possible if you really want to do that though, as is shown in the other answer. – Obsidian Age Feb 08 '18 at 22:17
  • Reading the pop up question, which is the same to mine, I think I may have to do it by hand. T-T Have to shift the 92 Databases base on where the players go. At least I already have the update code to move someone completely. Bit of a perfectionist, would rather get rid of all the dead numbers and compact the list. Thanks for the help and now I know what to do when I need to reorder a list that doesn’t effect so many others. – Elizabeth Stout Feb 08 '18 at 22:17
1

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

Pevara
  • 14,242
  • 1
  • 34
  • 47