Recount SQL auto increment ID while keeping the other columns intact-CodePudding

I have a table like this

ID	NAME
1	MICHAEL
2	JORDAN
5	DONALD
7	JAYCE
8	ROY
11	JOHN
16	DOE

Is there a way to recount the ID from the beggining so there is a sequel and keeping the others columns intact to be like this:

ID	NAME
1	MICHAEL
2	JORDAN
3	DONALD
4	JAYCE
5	ROY
6	JOHN
7	DOE

Thank you!

CodePudding user response：

If your MySQL version supports window function, you could use ROW_NUMBER.

Suppose you have the following table:

create table test(
ID int NOT NULL ,
NAME VARCHAR (25)
);

insert into test values 
(1,'MICHAEL'),
(2,'JORDAN'),
(5,'DONALD'),
(7,'JAYCE'),
(8,'ROY'),
(11,'JOHN'),
(16,'DOE');

Using row_number would give:

select ID,NAME, row_number() over(order by ID ASC ) as rownum
from test

Result:

ID    NAME    rownum
1   MICHAEL     1
2   JORDAN      2
5   DONALD      3
7   JAYCE       4
8   ROY         5
11  JOHN        6
16  DOE         7

Now, create another table test2.

create table test2(
ID int NOT NULL ,
NAME VARCHAR (25)
);

We can use INSERT INTO SELECT:

INSERT INTO test2( ID, NAME )
  WITH cte AS (
            select ID,NAME, row_number() over(order by ID ASC ) as rownum
            from test t1
  )
  SELECT rownum,NAME
  FROM cte;

select * from test2;

Result:

ID    NAME
1 MICHAEL
2 JORDAN
3 DONALD
4 JAYCE
5 ROY
6 JOHN
7 DOE

See fiddle