0

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Phaelax z
  • 1,814
  • 1
  • 7
  • 19
  • There's quite a few examples of "Splitting a delimited string into rows". [This question](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) has some great answers that should help solve this with straight sql so you don't have to solve with a painful loop. – JNevill Mar 19 '21 at 15:11

1 Answers1

1
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);
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828