I inherited a spreadsheet with 1600+ records, which I simply inserted into a single table (mapping) in MySQL.
I'd like to use the power of MySQL to help me do some "normalization", à la the MySQL Wizardy article I read 15 years ago. But, that article was about selecting data, not inserting.
I created a Targets table, with ID, Goal and Target. Goal is the integer part seen in mapping.Targets. Target is the fractional part (sometimes a letter) seen in mapping.Targets.
I created a mapping_targets table with ID, mappingID, targetID.
Is there a way using MySQL to insert records into mapping_targets using the data from the mapping table?
Something like: INSERT INTO mapping_targets (mappingID, targetID) SELECT mapping.id from mapping, (SELECT targets.id from targets where mapping.targets like concat ("%", targets.goal, ".", targets.target, "%"))
for the moment, I'm doing it manually, because there's only 1600 records in total, and only 34 records in Targets, but is there a clever way to do it in one swell foop?