0

I am having problem to update the list of id number again starting from 1,2,3,4,5. Since I have deleted few records as I was testing the sql commands. Can you please help on how to make this id column again starting from 1.

I could just the name of the id number however if I do that then when I input new record, it will again start from the previous number which was 66. 

ID    Name 

1   A

32  B

34  C

35  D

55  E

66  F
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
JustLift
  • 153
  • 1
  • 7
  • 17
  • Since they are all auto-incrementing, You have to empty or truncate the table – ntgCleaner Mar 18 '14 at 19:03
  • Look here http://stackoverflow.com/questions/740358/mysql-reorder-reset-auto-increment-primary-key – Jack Price-Burns Mar 18 '14 at 19:04
  • @ntgCleaner, You mean replace them manually? – JustLift Mar 18 '14 at 19:06
  • 1
    *Don't* do this. An auto-increment surrogate PK *does not* imply sequential order. It's better for the database clustering to simply let the auto-increment PK just keep increment as normal. – user2864740 Mar 18 '14 at 19:14
  • @user2864740 It seems that Sandesh is done with his testing and needs to reset his table to start fresh with real date. Truncating the table will do nothing to his IDs assuming he's starting fresh. – ntgCleaner Mar 18 '14 at 19:16
  • @ntgCleaner Then my previous "*don't*" still applies. If IDs are special or well-known, then reserve a range at the start (say, first 1000 IDs) and *assign the ID manually for these records*. However, the auto-increment PK still should not be used for a magical sequential order. – user2864740 Mar 18 '14 at 19:17
  • @SandeshMgr I work in a mySQL environment and the script for me is `TRUNCATE [TABLE] tbl_name` This will delete ALL RECORDS in your table and reset the counter at 1 – ntgCleaner Mar 18 '14 at 19:17
  • @nthCleaner, so I shouldn't use truncate command right? I did use it but nothing was the outcome. I am using workbench to implement the mysql database. I have tried all other commands provided from you guys and other people but still not working. – JustLift Mar 18 '14 at 19:22
  • Could use vba -- Change the ID field to int, take off autoincrement and unique constraint, open the table in a recordset, then do a for loop and loop til EOF updating the IDs? Or if you cant get away with removing PK constraint, add a new Int field, "New_Id", loop through it in a recordset with a for loop updating to new IDs, set that to PK, drop the PK constraint on original ID field, update original ID field to New_ID values, then move PK back to the old field and drop column new_id -- would work if that table isnt too big – Acantud Mar 18 '14 at 20:57
  • @Acantud, Can I use this loop command process in putty or workbench ? – JustLift Mar 18 '14 at 21:05
  • I'm not familiar with putty and i cant think of a way to do it in workbench. Can I suggest another easier option, if you are under 60,000 records? Export everything to excel, set the ID fields manually, then truncate the tables as the others suggested and re-upload. If I was experiencing the same problem I would do that, or if it was too big, I would do the loop in MS Access using VBA. You can declare a recordset to your table, then use a "Do until myRecordst.eof" and "myRecordset.movenext" , changing each value to "myRecordset.fields("id") = myIntCounter", then increment the intCounter – Acantud Mar 18 '14 at 22:07

4 Answers4

0

Truncate your table first and then execute this

   ALTER TABLE tablename AUTO_INCREMENT = 1
rakeshjain
  • 1,791
  • 11
  • 11
  • why on earth would you truncate the table? – Samuel Cook Mar 18 '14 at 19:07
  • @rakeshjain, How can I truncate the table. Can you help me please – JustLift Mar 18 '14 at 19:08
  • @SandeshMgr You can just use DELETE FROM tablename command to delete all the existing rows and then reset auto increment to 1 using above command so that new entries now will start from 1 – rakeshjain Mar 18 '14 at 19:11
  • @rakeshjain, If I deleted all my records and insert the command ALTER TABLE tablename AUTO_INCREMENT = 1 then it works. However all my files will be deleted isn't? So there is no way that I can just update the list not manually. – JustLift Mar 18 '14 at 19:14
  • @SandeshMgr You mean to say you want to preserve your old rows and have their ids renewed starting from 1 and then the auto-increment field should take the values from there on? – rakeshjain Mar 18 '14 at 19:22
0

You should truncate the table to reseed it properly and not just use alter table

fuzzybear
  • 2,325
  • 3
  • 23
  • 45
0

(tldr; it's usually better not to worry about the density or sequential order an auto-increment column.)

It is not possible1 to use an AUTO_INCREMENT to automatically fill in values less than MAX(ID).

However, the auto increment ID can be reset if existing IDs are updated. The compacting phase is required because MySQL does not allow "filling in gaps" via an auto-increment column.

  1. Compact the existing IDs, like so:

    SET @i := 0;
UPDATE t id = @i := (@i+1)

    Important: Make sure that all relational usage is identified in the form of Foreign Key relations with CASCADE ON UPDATE before this is done or the data may become irreversibly corrupted.

  2. Assign the auto-ID see to the maximum1 ID value after compacting:

    ALTER TABLE t AUTO_INCREMENT = (SELECT MAX(id) FROM t)

1 Per the AUTO_INCREMENT documentation in ALTER TABLE:

You cannot reset the counter to a value less than or equal to the value that is currently in use .. if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

The rule means that it is not possible to set the increment ID lower than an already used ID; in addition, manually assigning a value higher will automatically raise the AUTO_INCREMENT value.

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
  • @SandeshMgr `@i` is a variable called `@i`. This odd UPDATE syntax is just a common way of performing this task because MySQL lacks a proper ROW_NUMBER-like view operator and this approach (of a "side effect increment of a variable during the update") is easier than introducing outside number tables. – user2864740 Mar 18 '14 at 20:13
  • SET @i := 0 UPDATE files id = @i := (@i+1) Still doesn't work. @user2864740. It does accepts SET @i := 0 this command but not UPDATE files id = @i := (@i+1), gives me errors. – JustLift Mar 18 '14 at 20:16
  • @SandeshMgr Whoops, there needs to be a `;` after the SET statement or else it will be a syntax error. I've updated the post. – user2864740 Mar 18 '14 at 20:22
  • @SandeshMgr To just display the *sequential number* of the record, that should be done from code with an ORDER BY in the SQL, instead of messing with the ID field. – user2864740 Mar 18 '14 at 20:24
  • I wanted the list to reset again starting from 1. I was doing some testing on some of the function therefore there were few rows created therefore I had to download these ones. Now, I want the list of number to go again from 1 keeping some records which are already in the table. – JustLift Mar 18 '14 at 20:28
  • @SandeshMgr That is **not possible** with auto incremenet, as per the links I've included. You must manually assign ID's *lower* than the current max(ID) value. – user2864740 Mar 18 '14 at 20:30
  • @SandeshMgr Using the compacting approach above *with* proper FK/relational updates is the "best" that can be done automatically. However, I highly recommend *not* viewing auto-increment IDs as a dense rank, because this is *not* their purpose. – user2864740 Mar 18 '14 at 20:43
0

The easiest (and sometimest fastest) way is to remove column and add it back. Updating column may screw up indexes or make a mess with values. Droping whole table got no sense. But remember that if other columns refer to that ids you can damage your app.

Irek Kubicki
  • 67
  • 3