0
primary │  id   │   stuff
   1        1      ......
   2        2      ......
   3        3      ......
   4        4      ......
   5        5      ......

This is my table. Lets say i deleted table.primary = 3. After that i want to change table.id=4 as id=3 and table.id=5 to id=4. Because i need to rank id one by one. How can i do that after deleting table.primary=3? Thanks.

  • 1
    There are easily a dozen ways this will happen. I think I have about 10 screenshots of simple attempts to force gaps to occur by now. Here is another thing to consider [here](http://stackoverflow.com/a/38363271). You will never succeed at plugging holes without going bonkers, and don't forget the FK's and the potential (read: certainty) of errors. We are risk adverse people, or *should* be. – Drew Jul 26 '16 at 21:18
  • as juergen d already mentioned, do not mess with ids. ever. you will pull in many hard to debug problems! ids should just keep their value, because that's what ids are meant to be ... a value that will always (as long as a record exists) identify / reference an entry. if your id field not really is an id, you should name it differently. why do you have a field "primary" as well, because primary has a similar meaning. who should understand that? you should care about naming columns (and variables, functions, classes, files, ... for that matter) that it transports meaning. – Jakumi Jul 26 '16 at 21:44

1 Answers1

3

You don't!

Never mess with the auto-increment keys! Use another column for ranking, like a created column with the timestamp you added the record. Then you can build the ranking on-the-fly in your select statement. Like this

select *, @rank := @rank + 1 as ranking
from your_table
cross join (select @rank := 0) r
order by created
Community
  • 1
  • 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • This is a slam dunk. +1 – Drew Jul 26 '16 at 21:02
  • thanks for the answer however i am unable to understand you mysql code. What is `r`? –  Jul 26 '16 at 21:12
  • `r` is just a random alias name for the subquery `(select @rank := 0)` which is a simple variable initialization. – juergen d Jul 26 '16 at 21:16
  • 1
    He is bringing a variable (`@rank`) into the mix. It happens with a simple cross join, and that table is a Derived Table, and every Derived Table needs a name. Otherwise it errors out. So he chose `r`. It could have just as well have been `xParameters` or anything. – Drew Jul 26 '16 at 21:20
  • ok, i understood your answer correctly however it does not solve my problem. i need to store sorting number in the database bc i am gonna call new `stuff`s with an `AJAX` call. For instance `id=5` will call `id=2` when it gets a click on it. –  Jul 26 '16 at 21:26
  • If you need a column to fetch then you can create a **View** out of this query which behaves like a normal table but is in fact a user-defined query. – juergen d Jul 26 '16 at 21:53