Is it possible to create group_id using repeating textual information from another column

Question

Let's say we have a table like

id | group_id | TEXT | -------------------------------------- 1 | | NBA -------------------------------------- 2 | | NHL -------------------------------------- 3 | | NBA -------------------------------------- 4 | | NHL -------------------------------------- 5 | | NHL -------------------------------------- 

Is it possible to create group_id with a MySQL function or query or something :) using the fact of repetition (duplication) of text in a TEXT column? have such a table

 id | group_id | TEXT | -------------------------------------- 1 | 10 | NBA -------------------------------------- 2 | 11 | NHL -------------------------------------- 3 | 10 | NBA -------------------------------------- 4 | 11 | NHL -------------------------------------- 5 | 11 | NHL -------------------------------------- 

Answer 1

You can try using HEX :

 SELECT id, HEX(Text) as group_id, Text FROM Tbl 

If you want a decimal value, you can convert it from hex:

 SELECT id, CONV(HEX(Text), 16, 10) as group_id, Text FROM Tbl 

Result:

 ID GROUP_ID TEXT 1 5128769 NBA 2 5128769 NBA 3 5130316 NHL 4 5130312 NHH 5 5130316 NHL 8 4342081 BAA 9 4342081 BAA 

SQLFiddle

Answer 2

 CREATE TEMPORARY TABLE nums (n int AUTO_INCREMENT PRIMARY KEY, txt varchar(4)) AUTO_INCREMENT=10; INSERT INTO nums (txt) SELECT DISTINCT text FROM table1; UPDATE table1 INNER JOIN nums ON txt=text SET group_id=n 

SQLfiddle

Answer 3

Looking at my old answer, I just thought of another way to solve the problem without the tmp table:

 CREATE Table tbl (id int,grpid int, text varchar(10)); INSERT INTO tbl (id,text) VALUES (1,'NBA'),(2,'NBA'), (3,'NHL'),(4,'NHH'),(5,'NHL'),(8,'BAA'),(9,'BAA'); SET @i:=100; -- set the start sequence number for grpid UPDATE tbl INNER JOIN ( SELECT @i: =@i +1 gid,text FROM ( SELECT DISTINCT text FROM tbl ORDER BY text ) dt ) gi ON tbl.text=gi.text SET tbl.grpid=gi.gid; 

tbl after UPDATE :

 | ID | GRPID | TEXT | --------------------- | 1 | 102 | NBA | | 2 | 102 | NBA | | 3 | 104 | NHL | | 4 | 103 | NHH | | 5 | 104 | NHL | | 8 | 101 | BAA | | 9 | 101 | BAA | 

sqlfiddle