Normalizing an existing table I made a decade ago. I have a varchar column (platforms) with a list of numbers that I'd like to break down and place into a separate table.
[table_A]
id int
platforms varchar
other fields....
An example for platform would look something like:
"1,3,4,7"
Those numbers link to another table called Platforms, which is nothing more than a matching ID and a varchar field (a description). I've created a new table to make the associations between table_A and Platforms.
[Table_B]
id int
table_A_id int
platform_id
So for the above platform sample, I'd have 4 rows inserted into Table_B. My question is, can I do this strictly through SQL? Only thing I can think of is writing a php script to parse the varchar and do individual inserts on each row in table_A. Is there a more elegant way without resorting to an external script?
INSERT INTO Table_B (table_A_id, platform_id)
SELECT a.id, p.id
FROM table_A AS a JOIN Platforms AS p ON FIND_IN_SET(p.ID, a.platforms);