Link to home
Start Free TrialLog in
Avatar of Mark_Co
Mark_CoFlag for United States of America

asked on

MySQL beginner query ignorance. Changing the table id value so it places everything once again in numerical order

Hello, I am new to programming and am watching tutorials online this summer in my free time. I know this is a simple question but I've searched online and I can't find exactly what I need.

I deleted a row so now i no longer have "id 2 3 4 5". I'm left with 2, 4 ,5". I don't know the MySQL query to place alie smith as #3 and james smith as #4. Please help me out with this query. Thanks

     id  name            age
    2   Sandy Smith     21
    4   Alie Smith        19
    5   James Smith     11

Open in new window

Avatar of Marco Gasi
Marco Gasi
Flag of Spain image

You don't need to do it. id field doesn't need to be ordered that way: it only gives an unique id to each record
Avatar of Mark_Co


Ok. Thank you. But just for my own sanity, IF I wanted to, "SELECT *
FROM table
ORDER BY id" isn't doing it. What would it be?
SELECT @rownum:=@rownum+1, name, age FROM tablename

This will render:
            name            age
    1   Sandy Smith     21
    2   Alie Smith        19
    3   James Smith     11

You have to create a new column using the rownum since the id field is a permanent designator that should not change.
Excuse me, but why should you order database records by id? Id, is a non-menaing identifier, usewd internally by MySql. If you need to give your users let's say a rank, create a neew field and set its value when users (or whatever they be) log in or register or hwn you want.
Avatar of Mark_Co


@ Jamesrh thanks. Now the former id column has NULL values and the column name appears as @rownum:=@rownum+1. So to re insert values and have it auto increment when I put innew values, how do I do this?
Avatar of jamesrh
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mark_Co


Thank you