CODEWITHSUNDEEP
mysqldatabase
Sahil Mittal
Sahil Mittal

Reputation: 20753

How to add a column with auto-incremented values to a table of mySql database?

I have a table with the following structure-

  Col-1      Col-2     
(primary)

   234        A        
   154        B
   987        B
   374        C
   983        C
   364        C
   873        A

I've to insert a column, say Col-3 to this table with auto-incremented values starting from 0, based on Col-2 value. For the above table I want the values to be like-

  Col-1      Col-2      Col-3
(primary)

   234        A           1
   154        B           1
   987        B           2
   374        C           1
   983        C           2
   364        C           3
   873        A           2

The table is static, so if possible, I want to run the query directly in phpmyadmin.

Any help would be appreciated, thanks.

Upvotes: 1

Views: 289

Answers (2)

Anthony
Anthony

Reputation: 37045

This isn't possible directly per your specification and will require a trigger which would set the value of Col-3 using COUNT(Col-2). I think this would work, but test to be sure:

CREATE TRIGGER col2_count AFTER INSERT ON your_table
FOR EACH ROW SET col3 = COUNT(1) WHERE col2 = NEW.col2;

Upvotes: 0

John Woo
John Woo

Reputation: 263683

You can do it using correlated subquery.

SELECT  A.Col1, 
        A.Col2, 
        (
            SELECT  COUNT(*)
            FROM    tableName c
            WHERE   c.Col2 = a.Col2 AND
                    c.Col1 <= a.Col1) AS RowNumber
FROM    TableName a
ORDER   BY a.Col2, RowNumber

Upvotes: 3

Related Questions