How to loop through comma separated values in field to create new rows in other table in MySQL
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 '18 at 15:15
GGadde
392114
asked Nov 21 '18 at 14:41
Drake MasterDrake Master
62
add a comment |
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 '18 at 15:15
GGadde
392114
asked Nov 21 '18 at 14:41
Drake MasterDrake Master
62
Yep search for mysql split string.
– P.Salmon
Nov 21 '18 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 '18 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 '18 at 19:22
add a comment |
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 '18 at 15:15
GGadde
392114
asked Nov 21 '18 at 14:41
Drake MasterDrake 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 '18 at 15:15
GGadde
392114
asked Nov 21 '18 at 14:41
Drake MasterDrake Master
62
edited Nov 21 '18 at 15:15
GGadde
392114
asked Nov 21 '18 at 14:41
Drake MasterDrake Master
62
edited Nov 21 '18 at 15:15
GGadde
392114
edited Nov 21 '18 at 15:15
GGadde
392114
edited Nov 21 '18 at 15:15
GGadde
392114
392114
asked Nov 21 '18 at 14:41
Drake MasterDrake Master
62
asked Nov 21 '18 at 14:41
Drake MasterDrake Master
62
asked Nov 21 '18 at 14:41
Drake MasterDrake Master
62
62
Yep search for mysql split string.
– P.Salmon
Nov 21 '18 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 '18 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 '18 at 19:22
add a comment |
Yep search for mysql split string.
– P.Salmon
Nov 21 '18 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 '18 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 '18 at 19:22
Yep search for mysql split string.
– P.Salmon
Nov 21 '18 at 14:53
Yep search for mysql split string.
– P.Salmon
Nov 21 '18 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 '18 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 '18 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 '18 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 '18 at 19:22
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 14:58
RogueRogue
7,03332854
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 '18 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 '18 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 '18 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 '18 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 '18 at 21:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
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
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 '18 at 14:58
RogueRogue
7,03332854
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 '18 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 '18 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 '18 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 '18 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 '18 at 21:14
add a comment |
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 '18 at 14:58
RogueRogue
7,03332854
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 '18 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 '18 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 '18 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 '18 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 '18 at 21:14
add a comment |
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 '18 at 14:58
RogueRogue
7,03332854
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 '18 at 14:58
RogueRogue
7,03332854
answered Nov 21 '18 at 14:58
RogueRogue
7,03332854
answered Nov 21 '18 at 14:58
RogueRogue
7,03332854
answered Nov 21 '18 at 14:58
RogueRogue
7,03332854
7,03332854
This pre-supposes that old_leagues is a foreign key to an existing table (or one which can be easily created).
– symcbean
Nov 21 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 21:14
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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 '18 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 '18 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 '18 at 19:22