Mysql Question posted in
The official MySQL documentation can be found here.

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

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
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search