Avatar of Mark_Co
Flag 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 undefined
Last Comment

8/22/2022 - Mon
Marco Gasi

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

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Marco Gasi

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.

@ 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?

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.