CODEWITHSUNDEEP
mysqlsqlgroup-bysql-update
aLt
aLt

Reputation: 218

UPDATE col based on GROUP BY

I have one table 

id |  title  | ...
––----------------
 1 |  test A | ...
 2 |  test C | ...
 3 |  test B | ...
 4 |  test C | ...
 5 |  test B | ...
 6 |  test X | ...

And I want to add a column based on GROUP BY query : SELECT * FROM table GROUP BY title

id |  title  | group_id (int)
––--------------------------
 1 |  test A | 1
 2 |  test C | 2
 3 |  test B | 3
 4 |  test C | 2
 5 |  test B | 3
 6 |  test X | 4

group_id doesn't need to be significant, it just have to be the same value on the row which have the same title value and different from the other.

How can I do this ?

Thanks.

PS: Sorry for my English, I'm french.

Upvotes: 0

Views: 228

Answers (1)

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

Well given you alter the table and add the new column ParentID ? Something like

UPDATE ATable t
  INNER JOIN (
    SELECT
      Title, 
      MIN(ID) AS ID
    FROM ATable
    GROUP BY Title
  ) m ON t.Title = m.Title
SET t.ParentID = m.ID

Upvotes: 1

Related Questions