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

CodePudding user response：

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

CodePudding user response：

1. Alter Table to add ID column

ALTER TABLE
      `your_table`
    ADD
      COLUMN `ID` INT NULL auto_increment;

2. 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