I have a table with col1 id int, col2 as varchar(comma seperated value) and column 3 for assigning group to them. Table looks like
col1 col2 group
..............................
1 2,3,4
2 5,6
3 1,2,5
4 7,8
5 11,3
6 22,8
this is only the sample of real data, now i have to assign a group no to them in such a way that output looks like
col1 col2 group
..............................
1 2,3,4 1
2 5,6 1
3 1,2,5 1
4 7,8 2
5 11,3 1
6 22,8 2
The logic for assigning group no is that every similar comma seperated value of string in col2 have to be same group no as every where in col2 where '2' is there it has to be same group no but the complication is that 2,3,4 are together so they all three int value if found in any where in col2 will be assigned same group. the major part is 2,3,4 and 1,2,5 both in col2 have 2 so all int 1,2,3,4,5 have to assign same group no. Tried store procedure with match against on col2 but not getting desired result
Most imp( i can't use normalization,because i cant afford to make new table from my original table which have millions of record) ,even normalization is not helpfull in my context.
This question is also on stackoverflow with bounty on this link
Achieved so far...... Ihave set the group column auto increment and then wrote this procedure:-
BEGIN
declare cil1_new,col2_new,group_new int;
declare done tinyint default 0;
declare group_new varchar(100);
declare cur1 cursor for select col1,col2,`group` from company ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
open cur1;
REPEAT
fetch cur1 into col1_new,col2_new,group_new;
update company set group=group_new where
match(col2) against(concat("'",col2_new,"'"));
until done end repeat;
close cur1;
select * from company;
END
This procedure is working,no syntex mistake but the proble is that i am not acheiving the desired result exectly.
