Reputation: 1013
Description
Following is Table Structure:
eligibility_table
ID COURSE_ID BRANCH_IDS
1 501 621,622,623
1 502
1 503 625
2 501 621
2 505 650
3 500
Now, I am making new table structure as describe below and inserting course_table,branch_table through eligibility_table. So following, final output I want
course_table
ID COURSE_ID
1 501
1 502
1 503
2 501
2 505
3 500
branch_table
ID BRANCH_ID
1 621
1 622
1 623
1 625
2 621
2 650
Problem:
I am struggling to write SQL QUERY for branch_table. I want to write a query like
INSERT INTO branch_table SELECT --- from eligibility_table --
Upvotes: 4
Views: 3958
Reputation: 92845
UPDATED You can do it with SQL like this
INSERT INTO branch_table (id, branch_id)
SELECT e.id, SUBSTRING_INDEX(SUBSTRING_INDEX(e.branch_ids, ',', n.n), ',', -1) branch_id
FROM eligibility_table e CROSS JOIN
(
SELECT a.N + b.N * 10 + 1 n
FROM
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
ORDER BY n
) n
WHERE n.n <= 1 + (LENGTH(e.branch_ids) - LENGTH(REPLACE(e.branch_ids, ',', '')))
ORDER BY id, branch_id
UNION ALL
and CROSS JOIN
. Sometimes it's handy to have a real tally table in your db.SUBSTRING_INDEX()
gets everything up to n'th element in a list and outer SUBSTRING_INDEX()
extract right most part after a last delimiter effectively getting n-th element itself.CROSS JOIN
allows us to produce a set of rows which is a cartesian product (of 100 rows in n and all rows in eligibility_table)WHERE
clause filters out all unnecessary rows from the resultset Note: this query will split up to 100 branch ids. If you need more or less you can adjust a limit by editing the inner subquery
Result in branch_table:
| ID | BRANCH_ID | ------------------ | 1 | 621 | | 1 | 622 | | 1 | 623 | | 1 | 625 | | 2 | 621 | | 2 | 650 |
Here is SQLFiddle demo
Upvotes: 4
Reputation: 64439
If I'm correct in that you are changeing your database structure so it is actually normalized, and this is a one time thing, I'd suggest you do this in code. Connect to the database, read your old tables and insert the new. You can add some error-checking and exceptionhandling as well!
Just take your favorite language, and then do a standard split()
on that comma-separated value, and insert those values in the table. If it doesn't work you can print out what line went wrong so you can manually fix those issues. (that will be much harder in sql to accomplish)
Upvotes: 3