0

I have a database listing individuals and their qualifications. Unfortunately the qualifications are in one field and separated by semicolons--this is very limiting for the database and not ideal. I'm trying to query the table, loop through the rows, split the qualifications field, and insert them into a new table called qualifications.

The profile table looks like:

  • id; a unique number, like: 5
  • quals; semicolon separated field, like: cpr;firstaid;lifeguard
  • additional fields that are irrelevant

It's not valid, but so far I have: INSERT INTO qualifications (id, qual) SELECT id, SPLIT(';', quals) FROM profile;

With the example data, I'm trying to insert three rows into the qualifications table:

5, cpr
5, firstaid
5, lifeguard
GFL
  • 1,278
  • 2
  • 15
  • 24
  • Fortunately you can fix your data model. Get back to us once you do. – Strawberry May 19 '18 at 07:11
  • 2
    The split part and inserting part should be done in application code – Mạnh Quyết Nguyễn May 19 '18 at 07:11
  • @MạnhQuyếtNguyễn, originally I was thinking of downloading all the rows in PHP, splitting it, then inserting all the fields back in. I thought that would be pretty taxing and was thinking of doing it in a loop through in MySQL. But a mass insert like this would be the best way to go? https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql – GFL May 19 '18 at 07:22
  • Your task sounds like a cron scheduled task. Which run on specific time (like daily at 0h00...). So performance is not the first priority consider here. Don't worry – Mạnh Quyết Nguyễn May 19 '18 at 07:41

0 Answers0