How to add Auto Increment Id in MySQL table based on sorted sequence of another column?

user8EwG 0
Viewed 46

I have a table where I want to add auto Incrementing Id which isn't there. The rows aren't sorted. I want to add ID column based on the lexicographical order of another column, As shown below:

CURRENT TABLE            AFTER ADDING ID

CLASS | ITEM           ID | CLASS | ITEM
------|-------         ---|-------|-------
fruits| banana          1 | fruits| apple
------|--------        ---|-------|-------
tools | hammer          2 | fruits| banana
------|--------        ---|-------|-------
fruits| apple           3 | flura | banyan
------|--------        ---|-------|-------
flura | banyan          4 | tools | hammer
------|--------        ---|-------|-------
fauna | human           5 | fauna | human
auto-increment mysql indexing database sql
2 Answers

My suggestion would be to use a programming language (Python is awesome for these kinds of use cases and I'll use that in my answer). The steps required would be as follows:

  1. Create a temp table in your database with the auto-increment field
  2. Query your database in Python and retrieve all rows
  3. Sort the list based on your desired field
  4. Insert the sorted data into the temp table
  5. Rename the current table to another name
  6. Rename temp to the current table
Arash M.0
  1. Alter Table to add ID column
ALTER TABLE
      `your_table`
    ADD
      COLUMN `ID` INT NULL auto_increment;
  1. Update your table
     UPDATE your_table  SET
        your_table.ID = a.ID, 
        your_table.CLASS = a.CLASS,
         your_table.ITEM = a.ITEM, FROM (
        SELECT  ROW_NUMBER() OVER(ORDER BY CLASS ASC) AS ID, CLASS, ITEM 
        FROM your_table) AS a WHERE 
        a.CLASS = your_table.CLASS
        a.CLASS = your_table.ITEM
Johnbosco Adam0
Related