8

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

Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
Krity Shrestha
  • 333
  • 1
  • 4
  • 10

4 Answers4

17

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;
luk4ward
  • 229
  • 1
  • 5
  • An auto_increment column is always also a primary key. You will run into duplicate key errors with this. Also you shouldn't mess with auto_inc columns at all... – fancyPants Mar 31 '17 at 09:36
  • @fancyPants the main purpose of the question is to order existing data, auto_increment already has value higher than there are rows in the table so nothing dangerous here – luk4ward Mar 31 '17 at 09:40
  • What about number 3? And you can't judge that based on 3 sample data rows. This answer is wrong in many ways! Delete it! – fancyPants Mar 31 '17 at 09:42
  • It will do exactly what it should do - reset all ids. Your advice tough for a question 'how' is 'don't'. There are cases when you want to do something what is not safe or necessary but maybe someone needs to do this, its not your call to judge – luk4ward Mar 31 '17 at 09:47
  • No, it will not. This works with a simple column, but not with an auto_increment. – fancyPants Mar 31 '17 at 09:47
  • Since you edited your comment, I'm clarifying once more. I don't argue about the why. Otherwise I would have downvoted the other answers as well. I wrote my opinion in my answer. If the OP follows this opinion is up to her decision. I'm arguing with you because your answer will fail with a probability of 99% – fancyPants Mar 31 '17 at 10:23
  • Probably you have a point but for auto_increment you just need to add something like `update table_name set id = 0-id;` before that and it won't fail – luk4ward Mar 31 '17 at 10:31
  • As of MySQL 8 you'll start getting this warning when you use this way of updating: WARNING: DB: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. (SQL State: HY000 - Error Code: 1287) – JRSofty Mar 10 '23 at 13:50
8

Is there any query to update my existing id and make my id start from 1 and next id 2 and so on

What you can do is transfer the content of your table to another table. Reset the auto increment counter, insert your data back into the original table but let MySQL assign the primary key.

Assuming your table name is mytable You do it like this:

CREATE TABLE mytable_tmp select * from mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable AUTO_INCREMENT = 1;
INSERT INTO mytable(name) SELECT name FROM mytable_tmp ORDER BY id;
DROP TABLE mytable_tmp;
nos
  • 223,662
  • 58
  • 417
  • 506
  • @KrityShrestha It is working fine for mysql. I just tested the exact SQL i posted here, and I have used similar constructs many times. Ofcourse you need to adjust the SQL to match your column names and table name – nos Mar 31 '17 at 09:34
  • @nos there is no need to create a temporary table, just take a look at my suggestion with session variable – luk4ward Mar 31 '17 at 09:36
  • @KrityShrestha I can't help your with anything more unless you describe what you did and what specific error you got. This works everywhere I've tried it. – nos Mar 31 '17 at 12:45
5

In my opinion you shouldn't mess with auto_increment columns at all. Let them be as they are. Their only job is to identify a row uniquely. If you want a nice serial number use another column (make it unique if you wish)!

You will always run into trouble and there will always happen things, that mess with your nice 1, 2, 3, ... sequence. A transaction gets rolled back? Boom, your sequence is 1, 2, 3, 5, ... instead of your intended 1, 2, 3, 4, ...

This can also be a very heavy operation. An auto_increment column is always also a primary key. Every other index on this table includes the primary key. Every time you reset your auto_increments, every index on this table is rewritten.

So my advice is, don't mess with auto_increments.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
2

This query will work for your scenario:

ALTER TABLE tablename DROP id

ALTER TABLE tablename ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1
Anju Augustin
  • 47
  • 2