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

Mark_Co
Mark_Co used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Marco GasiFreelancer
Top Expert 2010

Commented:
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

Author

Commented:
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?

Commented:
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.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Marco GasiFreelancer
Top Expert 2010

Commented:
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.

Author

Commented:
@ 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?
Commented:
Before I say anything else it would be helpful to know the what and the why of this change.  Is it just that you want the id's in sequence for aesthetic reasons?  The select statement I gave you wasn't intended to  reorder the id's but to give you a way to leave the id's alone and get a report that had what amounted to row numbers.  Unless you have a very good reason to change the primary key values for records in a table it is a bad idea.  


If you really need to do this, here is a stackoverflow thread on the subject: MySQL: Reorder/Reset auto increment primary key?

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial