1

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
Yasir
  • 332
  • 2
  • 9
  • Does this answer your question? [Add Auto-Increment ID to existing table?](https://stackoverflow.com/questions/14753321/add-auto-increment-id-to-existing-table) – Mihe Sep 10 '22 at 10:51

2 Answers2

2

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.
  • 61
  • 5
  • Yeah. I eventually did that. But was looking for some native way to do that. – Yasir Sep 14 '22 at 09:20
  • 2
    Steps 2,3,4 are simply the single SQL: `INSERT INTO temp SELECT ... ORDER BY ...;` – Rick James Sep 14 '22 at 15:15
  • 1
    @RickJames Yeah you're right. I was thinking of the most general way to do the sorting but for this use case this query totally works without the need of programming. – Arash M. Sep 18 '22 at 07:09
  • 1
    There's the old addage "If you have a hammer, everything looks like a nail." Your hammer is Python; mine is SQL. – Rick James Sep 18 '22 at 17:24
1
  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 Adam
  • 81
  • 1
  • 11