How to loop through comma separated values in field to create new rows in other table in MySQL
up vote
1
down vote
favorite
I have two tables I'm working within MySQL, one has some data, the other will be populated with this data, however - there is one field in the table that has comma separated values that I need to use to create separate (similar) records.
For example:
DATA TABLE: "students"
Screenshot of table data is coming from
The data will go into a table called "student_season_cross" and will populate fields called "student_id" and "league_id", but I want it to base the new records on "old_leagues" in the original table.
So, for example: data in the original table looking like this:
student_id | old_leagues
38 | 9, 33
39 | 8
40 | 3, 46
Would populate the new table like this:
ss_index | student_id | league_id
auto increment | 38 | 9
auto increment | 38 | 33
auto increment | 39 | 8
auto increment | 40 | 3
auto increment | 40 | 46
I was thinking it would be some kind of loop, but I just can't wrap my head around it. Any ideas?
mysql
edited Nov 21 at 15:15
GGadde
335113
asked Nov 21 at 14:41
Drake Master
62
add a comment |
up vote
1
down vote
favorite
I have two tables I'm working within MySQL, one has some data, the other will be populated with this data, however - there is one field in the table that has comma separated values that I need to use to create separate (similar) records.
For example:
DATA TABLE: "students"
Screenshot of table data is coming from
The data will go into a table called "student_season_cross" and will populate fields called "student_id" and "league_id", but I want it to base the new records on "old_leagues" in the original table.
So, for example: data in the original table looking like this:
student_id | old_leagues
38 | 9, 33
39 | 8
40 | 3, 46
Would populate the new table like this:
ss_index | student_id | league_id
auto increment | 38 | 9
auto increment | 38 | 33
auto increment | 39 | 8
auto increment | 40 | 3
auto increment | 40 | 46
I was thinking it would be some kind of loop, but I just can't wrap my head around it. Any ideas?
mysql
edited Nov 21 at 15:15
GGadde
335113
asked Nov 21 at 14:41
Drake Master
62
Yep search for mysql split string.
– P.Salmon
Nov 21 at 14:53
I think there's a better solution to be had withFIND_IN_SETand a join, working on that now for an answer
– Rogue
Nov 21 at 14:53
This will require number generator table to be used. At max how many comma separatedleague_idvalues are expected in a single field ?
– Madhur Bhaiya
Nov 22 at 19:22
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I have two tables I'm working within MySQL, one has some data, the other will be populated with this data, however - there is one field in the table that has comma separated values that I need to use to create separate (similar) records.
For example:
DATA TABLE: "students"
Screenshot of table data is coming from
The data will go into a table called "student_season_cross" and will populate fields called "student_id" and "league_id", but I want it to base the new records on "old_leagues" in the original table.
So, for example: data in the original table looking like this:
student_id | old_leagues
38 | 9, 33
39 | 8
40 | 3, 46
Would populate the new table like this:
ss_index | student_id | league_id
auto increment | 38 | 9
auto increment | 38 | 33
auto increment | 39 | 8
auto increment | 40 | 3
auto increment | 40 | 46
I was thinking it would be some kind of loop, but I just can't wrap my head around it. Any ideas?
mysql
edited Nov 21 at 15:15
GGadde
335113
asked Nov 21 at 14:41
Drake Master
62
I have two tables I'm working within MySQL, one has some data, the other will be populated with this data, however - there is one field in the table that has comma separated values that I need to use to create separate (similar) records.
For example:
DATA TABLE: "students"
Screenshot of table data is coming from
The data will go into a table called "student_season_cross" and will populate fields called "student_id" and "league_id", but I want it to base the new records on "old_leagues" in the original table.
So, for example: data in the original table looking like this:
student_id | old_leagues
38 | 9, 33
39 | 8
40 | 3, 46
Would populate the new table like this:
ss_index | student_id | league_id
auto increment | 38 | 9
auto increment | 38 | 33
auto increment | 39 | 8
auto increment | 40 | 3
auto increment | 40 | 46
I was thinking it would be some kind of loop, but I just can't wrap my head around it. Any ideas?
mysql
mysql
edited Nov 21 at 15:15
GGadde
335113
asked Nov 21 at 14:41
Drake Master
62
edited Nov 21 at 15:15
GGadde
335113
asked Nov 21 at 14:41
Drake Master
62
edited Nov 21 at 15:15
GGadde
335113
edited Nov 21 at 15:15
GGadde
335113
edited Nov 21 at 15:15
GGadde
335113
335113
asked Nov 21 at 14:41
Drake Master
62
asked Nov 21 at 14:41
Drake Master
62
asked Nov 21 at 14:41
Drake Master
62
62
Yep search for mysql split string.
– P.Salmon
Nov 21 at 14:53
I think there's a better solution to be had withFIND_IN_SETand a join, working on that now for an answer
– Rogue
Nov 21 at 14:53
This will require number generator table to be used. At max how many comma separatedleague_idvalues are expected in a single field ?
– Madhur Bhaiya
Nov 22 at 19:22
add a comment |
Yep search for mysql split string.
– P.Salmon
Nov 21 at 14:53
I think there's a better solution to be had withFIND_IN_SETand a join, working on that now for an answer
– Rogue
Nov 21 at 14:53
This will require number generator table to be used. At max how many comma separatedleague_idvalues are expected in a single field ?
– Madhur Bhaiya
Nov 22 at 19:22
Yep search for mysql split string.
– P.Salmon
Nov 21 at 14:53
Yep search for mysql split string.
– P.Salmon
Nov 21 at 14:53
I think there's a better solution to be had with
FIND_IN_SET and a join, working on that now for an answer– Rogue
Nov 21 at 14:53
I think there's a better solution to be had with
FIND_IN_SET and a join, working on that now for an answer– Rogue
Nov 21 at 14:53
This will require number generator table to be used. At max how many comma separated
league_id values are expected in a single field ?– Madhur Bhaiya
Nov 22 at 19:22
This will require number generator table to be used. At max how many comma separated
league_id values are expected in a single field ?– Madhur Bhaiya
Nov 22 at 19:22
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
This is one of the few times that FIND_IN_SET comes in handy. You can use this function to retrieve (specific) segments of a comma-separated list. Using this, we can join any student record to a record of the available leagues; thus going for a result like:
league | student
==============
1 | 38
1 | 39
1 | 40
2 | 39
3 | 22
3 | 23
Here's where you can use FIND_IN_SET to determine student participation in a league. I'm also assuming a table named leagues where you have all of the league ids under leagues.id:
INESRT INTO new_leagues (student_id, league_id)
SELECT origial_table.student_id
leagues.id
FROM leagues
INNER JOIN original_table
-- join a student record every time FIND_IN_SET matches
ON FIND_IN_SET(leagues.id, original_table.old_leagues) >= 0
You'll have to forgive my inability to test this at the moment as I'm working on an upgrade to my development server, but hopefully you can glean a working answer from here.
answered Nov 21 at 14:58
Rogue
6,75732751
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 at 15:05
doesn't have to be a foreign key, but that would speed up the results. I would definitely recommend it for the new table, but I'm not actually certain there even is aleaguestable. I made it for the simplicity of porting things over (sometimes a few dirty steps is easier than a single query for porting these things).
– Rogue
Nov 21 at 15:08
Where in the OPs question is the table "original_table" described? You seem to be confusing the concept of a foreign key with the DDL declaration of a foreign key.
– symcbean
Nov 21 at 15:19
I tried that (slight variation to accommodate table names)... but it created an entry for each student and league instead of adhering to the values in the 'old_leagues' field. I then tried this, but this didn't work either: INSERT INTO student_season_cross SET student_id = archive_students.student_id, league_id = leagues.league_id, season_id = leagues.season_id WHERE league_id IN(archive_students.old_leagues)
– Drake Master
Nov 21 at 15:37
@symcbean the OP didn't offer any table names when I answered, they're just placeholders for the columns described. Drake: I'll do an sqlfiddle in about 6 hours to verify the statement is correct and functional for you (on a drive at the moment).
– Rogue
Nov 21 at 21:14
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
This is one of the few times that FIND_IN_SET comes in handy. You can use this function to retrieve (specific) segments of a comma-separated list. Using this, we can join any student record to a record of the available leagues; thus going for a result like:
league | student
==============
1 | 38
1 | 39
1 | 40
2 | 39
3 | 22
3 | 23
Here's where you can use FIND_IN_SET to determine student participation in a league. I'm also assuming a table named leagues where you have all of the league ids under leagues.id:
INESRT INTO new_leagues (student_id, league_id)
SELECT origial_table.student_id
leagues.id
FROM leagues
INNER JOIN original_table
-- join a student record every time FIND_IN_SET matches
ON FIND_IN_SET(leagues.id, original_table.old_leagues) >= 0
You'll have to forgive my inability to test this at the moment as I'm working on an upgrade to my development server, but hopefully you can glean a working answer from here.
answered Nov 21 at 14:58
Rogue
6,75732751
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 at 15:05
doesn't have to be a foreign key, but that would speed up the results. I would definitely recommend it for the new table, but I'm not actually certain there even is aleaguestable. I made it for the simplicity of porting things over (sometimes a few dirty steps is easier than a single query for porting these things).
– Rogue
Nov 21 at 15:08
Where in the OPs question is the table "original_table" described? You seem to be confusing the concept of a foreign key with the DDL declaration of a foreign key.
– symcbean
Nov 21 at 15:19
I tried that (slight variation to accommodate table names)... but it created an entry for each student and league instead of adhering to the values in the 'old_leagues' field. I then tried this, but this didn't work either: INSERT INTO student_season_cross SET student_id = archive_students.student_id, league_id = leagues.league_id, season_id = leagues.season_id WHERE league_id IN(archive_students.old_leagues)
– Drake Master
Nov 21 at 15:37
@symcbean the OP didn't offer any table names when I answered, they're just placeholders for the columns described. Drake: I'll do an sqlfiddle in about 6 hours to verify the statement is correct and functional for you (on a drive at the moment).
– Rogue
Nov 21 at 21:14
add a comment |
up vote
0
down vote
This is one of the few times that FIND_IN_SET comes in handy. You can use this function to retrieve (specific) segments of a comma-separated list. Using this, we can join any student record to a record of the available leagues; thus going for a result like:
league | student
==============
1 | 38
1 | 39
1 | 40
2 | 39
3 | 22
3 | 23
Here's where you can use FIND_IN_SET to determine student participation in a league. I'm also assuming a table named leagues where you have all of the league ids under leagues.id:
INESRT INTO new_leagues (student_id, league_id)
SELECT origial_table.student_id
leagues.id
FROM leagues
INNER JOIN original_table
-- join a student record every time FIND_IN_SET matches
ON FIND_IN_SET(leagues.id, original_table.old_leagues) >= 0
You'll have to forgive my inability to test this at the moment as I'm working on an upgrade to my development server, but hopefully you can glean a working answer from here.
answered Nov 21 at 14:58
Rogue
6,75732751
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 at 15:05
doesn't have to be a foreign key, but that would speed up the results. I would definitely recommend it for the new table, but I'm not actually certain there even is aleaguestable. I made it for the simplicity of porting things over (sometimes a few dirty steps is easier than a single query for porting these things).
– Rogue
Nov 21 at 15:08
Where in the OPs question is the table "original_table" described? You seem to be confusing the concept of a foreign key with the DDL declaration of a foreign key.
– symcbean
Nov 21 at 15:19
I tried that (slight variation to accommodate table names)... but it created an entry for each student and league instead of adhering to the values in the 'old_leagues' field. I then tried this, but this didn't work either: INSERT INTO student_season_cross SET student_id = archive_students.student_id, league_id = leagues.league_id, season_id = leagues.season_id WHERE league_id IN(archive_students.old_leagues)
– Drake Master
Nov 21 at 15:37
@symcbean the OP didn't offer any table names when I answered, they're just placeholders for the columns described. Drake: I'll do an sqlfiddle in about 6 hours to verify the statement is correct and functional for you (on a drive at the moment).
– Rogue
Nov 21 at 21:14
add a comment |
up vote
0
down vote
up vote
0
down vote
This is one of the few times that FIND_IN_SET comes in handy. You can use this function to retrieve (specific) segments of a comma-separated list. Using this, we can join any student record to a record of the available leagues; thus going for a result like:
league | student
==============
1 | 38
1 | 39
1 | 40
2 | 39
3 | 22
3 | 23
Here's where you can use FIND_IN_SET to determine student participation in a league. I'm also assuming a table named leagues where you have all of the league ids under leagues.id:
INESRT INTO new_leagues (student_id, league_id)
SELECT origial_table.student_id
leagues.id
FROM leagues
INNER JOIN original_table
-- join a student record every time FIND_IN_SET matches
ON FIND_IN_SET(leagues.id, original_table.old_leagues) >= 0
You'll have to forgive my inability to test this at the moment as I'm working on an upgrade to my development server, but hopefully you can glean a working answer from here.
answered Nov 21 at 14:58
Rogue
6,75732751
This is one of the few times that FIND_IN_SET comes in handy. You can use this function to retrieve (specific) segments of a comma-separated list. Using this, we can join any student record to a record of the available leagues; thus going for a result like:
league | student
==============
1 | 38
1 | 39
1 | 40
2 | 39
3 | 22
3 | 23
Here's where you can use FIND_IN_SET to determine student participation in a league. I'm also assuming a table named leagues where you have all of the league ids under leagues.id:
INESRT INTO new_leagues (student_id, league_id)
SELECT origial_table.student_id
leagues.id
FROM leagues
INNER JOIN original_table
-- join a student record every time FIND_IN_SET matches
ON FIND_IN_SET(leagues.id, original_table.old_leagues) >= 0
You'll have to forgive my inability to test this at the moment as I'm working on an upgrade to my development server, but hopefully you can glean a working answer from here.
answered Nov 21 at 14:58
Rogue
6,75732751
answered Nov 21 at 14:58
Rogue
6,75732751
answered Nov 21 at 14:58
Rogue
6,75732751
answered Nov 21 at 14:58
Rogue
6,75732751
6,75732751
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 at 15:05
doesn't have to be a foreign key, but that would speed up the results. I would definitely recommend it for the new table, but I'm not actually certain there even is aleaguestable. I made it for the simplicity of porting things over (sometimes a few dirty steps is easier than a single query for porting these things).
– Rogue
Nov 21 at 15:08
Where in the OPs question is the table "original_table" described? You seem to be confusing the concept of a foreign key with the DDL declaration of a foreign key.
– symcbean
Nov 21 at 15:19
I tried that (slight variation to accommodate table names)... but it created an entry for each student and league instead of adhering to the values in the 'old_leagues' field. I then tried this, but this didn't work either: INSERT INTO student_season_cross SET student_id = archive_students.student_id, league_id = leagues.league_id, season_id = leagues.season_id WHERE league_id IN(archive_students.old_leagues)
– Drake Master
Nov 21 at 15:37
@symcbean the OP didn't offer any table names when I answered, they're just placeholders for the columns described. Drake: I'll do an sqlfiddle in about 6 hours to verify the statement is correct and functional for you (on a drive at the moment).
– Rogue
Nov 21 at 21:14
add a comment |
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 at 15:05
doesn't have to be a foreign key, but that would speed up the results. I would definitely recommend it for the new table, but I'm not actually certain there even is aleaguestable. I made it for the simplicity of porting things over (sometimes a few dirty steps is easier than a single query for porting these things).
– Rogue
Nov 21 at 15:08
Where in the OPs question is the table "original_table" described? You seem to be confusing the concept of a foreign key with the DDL declaration of a foreign key.
– symcbean
Nov 21 at 15:19
I tried that (slight variation to accommodate table names)... but it created an entry for each student and league instead of adhering to the values in the 'old_leagues' field. I then tried this, but this didn't work either: INSERT INTO student_season_cross SET student_id = archive_students.student_id, league_id = leagues.league_id, season_id = leagues.season_id WHERE league_id IN(archive_students.old_leagues)
– Drake Master
Nov 21 at 15:37
@symcbean the OP didn't offer any table names when I answered, they're just placeholders for the columns described. Drake: I'll do an sqlfiddle in about 6 hours to verify the statement is correct and functional for you (on a drive at the moment).
– Rogue
Nov 21 at 21:14
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 at 15:05
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 at 15:05
doesn't have to be a foreign key, but that would speed up the results. I would definitely recommend it for the new table, but I'm not actually certain there even is a
leagues table. I made it for the simplicity of porting things over (sometimes a few dirty steps is easier than a single query for porting these things).– Rogue
Nov 21 at 15:08
doesn't have to be a foreign key, but that would speed up the results. I would definitely recommend it for the new table, but I'm not actually certain there even is a
leagues table. I made it for the simplicity of porting things over (sometimes a few dirty steps is easier than a single query for porting these things).– Rogue
Nov 21 at 15:08
Where in the OPs question is the table "original_table" described? You seem to be confusing the concept of a foreign key with the DDL declaration of a foreign key.
– symcbean
Nov 21 at 15:19
Where in the OPs question is the table "original_table" described? You seem to be confusing the concept of a foreign key with the DDL declaration of a foreign key.
– symcbean
Nov 21 at 15:19
I tried that (slight variation to accommodate table names)... but it created an entry for each student and league instead of adhering to the values in the 'old_leagues' field. I then tried this, but this didn't work either: INSERT INTO student_season_cross SET student_id = archive_students.student_id, league_id = leagues.league_id, season_id = leagues.season_id WHERE league_id IN(archive_students.old_leagues)
– Drake Master
Nov 21 at 15:37
I tried that (slight variation to accommodate table names)... but it created an entry for each student and league instead of adhering to the values in the 'old_leagues' field. I then tried this, but this didn't work either: INSERT INTO student_season_cross SET student_id = archive_students.student_id, league_id = leagues.league_id, season_id = leagues.season_id WHERE league_id IN(archive_students.old_leagues)
– Drake Master
Nov 21 at 15:37
@symcbean the OP didn't offer any table names when I answered, they're just placeholders for the columns described. Drake: I'll do an sqlfiddle in about 6 hours to verify the statement is correct and functional for you (on a drive at the moment).
– Rogue
Nov 21 at 21:14
@symcbean the OP didn't offer any table names when I answered, they're just placeholders for the columns described. Drake: I'll do an sqlfiddle in about 6 hours to verify the statement is correct and functional for you (on a drive at the moment).
– Rogue
Nov 21 at 21:14
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53414499%2fhow-to-loop-through-comma-separated-values-in-field-to-create-new-rows-in-other%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Yep search for mysql split string.
– P.Salmon
Nov 21 at 14:53
I think there's a better solution to be had with
FIND_IN_SETand a join, working on that now for an answer– Rogue
Nov 21 at 14:53
This will require number generator table to be used. At max how many comma separated
league_idvalues are expected in a single field ?– Madhur Bhaiya
Nov 22 at 19:22