Matty Clarke February 2016

Reset ID's without deleting rows (Not AUTO_INCREMENT)

I'm looking for a simple solution to get the ID column in my table to start from 0 and auto increment without deleting the rows or having to manually change the ID in the database.

I have around 120 rows in the table, the first record starts with the ID: 2, I want this as '0' and all the other rows to update in ascending order.

I understand the AUTO_INCREMENT would solve this solution if I deleted all my rows.

You can see by the way the numbers are ordered in the below table.

MySQL Table IDs

I hope I've explained myself properly and any help would be greatly appreciated.

Answers


Barmar February 2016

Write a subquery that generates the mapping from the old IDs to the new IDs. Join this with the table in an UPDATE query.

UPDATE yourTable AS t1
JOIN (
  SELECT id, @newID := @newID + 1 AS newID
  FROM yourTable
  JOIN (SELECT @newID := -1) AS vars
  ORDER BY id) AS new ON t1.id = new.id
SET t1.id = new.newID;

DEMO


Matty Clarke February 2016

Used the answer Barmar had provided, worked a treat!

Post Status

Asked in February 2016
Viewed 3,696 times
Voted 10
Answered 2 times

Search




Leave an answer