MySQL loop for every row and update

Question

I have table called users and for example it looks like:

  Name  ID 
  Tom    1
  Al    55 
  Kate  22

...

The problem is: the IDs are not in sequence.

I would like to give them new IDs from 1 to length of users. I would like to declare some var=1 and make UPDATE in loop and give them new ID = var, and later do var=var+1 until var <= users length

How can I do this?

Thank you very much!

Of course it isn't but I need to change it because I need it to make something different. I changing web app which was build a few years ago, DB and PHP code is really wrong wrote. I don't have time to rewrite everything from scratch. — Ridd
– Ridd, Commented Sep 17, 2016 at 12:57

TampaHaze · Accepted Answer · 2016-09-16 20:09:00Z

3

Here is how you would do that in MySQL. Just run this:

set @newid=0;
update users set ID = (@newid:=@newid+1) order by ID;

answered Sep 16, 2016 at 20:09

TampaHaze

1,1359 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Over a year ago

As version of "one query": update users set ID = (@newid:=@newid+1) where 0=(select @newid:=0) order by ID;

PeterHe · Accepted Answer · 2016-09-16 20:12:34Z

1

If the ID in the Users table is not referenced by other tables by FK, the following query can update the ID in the table to have new consecutive values:

CREATE TABLE IF NOT EXISTS tmpUsers (
  ID int not null, 
  newID int not null auto_increment primary key
) engine = mysisam;

INSERT INTO tmpUsers (ID,newID)
SELECT ID,NULL
FROM users
ORDER BY ID;

UPDATE users u INNER JOIN tmpUsers t 
ON u.ID=t.ID
SET u.ID=t.NewID;

DROP TABLE IF EXISTS tmpUsers;

Test script:

CREATE TABLE users (ID int not null, name nvarchar(128) not null);
INSERT users(ID,name)
VALUES (1,'aaa'),(4,'bbb'),(7,'ggg'),(17,'ddd');
SELECT * FROM users;

answered Sep 16, 2016 at 20:12

PeterHe

2,7861 gold badge11 silver badges7 bronze badges

Collectives™ on Stack Overflow

MySQL loop for every row and update

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related