How to update id set from 1?

Question

I have an id i.e primary key and auto increment. Is there any query to update my existing id and make my id start from 1 and next id 2 and so on..

For example

id  name
3   ABC
5   XYZ
9   PQR

NOTE: id is already primary and auto increment and I don't want truncate my id.

if possible i want to get

id  name
1   ABC
2   XYZ
3   PQR

ALTER TABLE table AUTO_INCREMENT = 1; is not my solution.

Thanks

Answer 1

Of course there is a way:

set @counter = 0;
update table_name
set id  = (@counter := @counter + 1);

EDIT

To avoid problem with duplicate keys you can run something like this before to temporary change current ids to negative equivalents:

update table_name
set id  = 0 - id;