I inherited a spreadsheet with 1600+ records, which I simply inserted into a single table (mapping) in MySQL.

mapping table

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?

  • Unclear. Are you trying to discover all the 6.x combinations? Perhaps then you want to make a table that 9,6,1, 9,6,2, 9,6,3, 10,6,1, etc, in order to be useful. – Rick James Oct 7 '18 at 16:26
  • No. I'm trying to find all the Targets that contain "6.1". For each of them, I want to add a new record to the mapping_targets that includes the ID from Targets as well as the Target.ID which corresponds to 6.1, ie. 1. And then the same for all the 6.2s, 6.3, etc. – Zonker.in.Geneva Oct 8 '18 at 6:40

This is a pretty clever CSV -> MySQL solution. Takes some pre-processing on the spreadsheet, but well worth it.

  • 1
    thanks, but the data has already been transferred into MySQL. I'm trying to take a field in one table to insert several fields in a second table. – Zonker.in.Geneva Sep 16 '18 at 10:23
  • 1
    Understood. Normalization is typically with respect to the design of the database, not the data. In this case -- barring other information not available here -- I'd still suggest backing up one step: design a normalized database schema; pre-process the data before inserting; then insert the data into the database. The type of manipulation I think you're wanting to do is begging for text-processing tools (awk ?), rather than data-processing tools. – Cary Reams Sep 16 '18 at 21:17
  • yes, it's with respect to the design of the database, but by definition, it involves manipulating the data that's already there. – Zonker.in.Geneva Sep 17 '18 at 16:37
  • 1
    Fair enough. Can you provide the structure of the involved tables? Maybe that will help me better understand what's desired. – Cary Reams Sep 17 '18 at 17:38
  • 1
    Not for me. Apologies. – Cary Reams Sep 21 '18 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.