I have two MySQL tables we can call
Both tables have a column called
PrizeGroupId. the goal is to create a one-to-one relationship between these columns, and I have created stored procedures to add/edit
Foo that update the corresponding row via the one-to-one relationship in
The problem lies in the fact that the data wasn't always structured this way and I need to write a script to convert the data from it's previous state (which I'm about to describe) to a one-to-one relationship based off of
Previously, multiple rows in
Foo could have the same
PrizeGroupId such that there was a one-to-many relationship between entries in
Foo based off of
PrizeGroupId. The script that I need to write has to break apart every one-to-many instance of this nature into many (almost identical) one-to-one relationships between
In principle, I want to:
- Iterate through
- See if the current row's
PrizeGroupIdis not unique in
- Assign it a unique value (perhaps the current items primary key)
- Add a row in
Barwith the new
PrizeGroupId. Copy over all of the old row's other data into this new row such that it is "nearly identical".
- After all is said and done, remove the old one-to-many row from
I understand the problem and how I could do this in pseudocode in a programming language, however I am still learning MySQL and am not sure how to go about solving a problem of this nature.
If you can provide me with help through MySQL code and/or what steps I can take/read about to go about solving this problem that would be, or at least point me to the kind of reading/SO question related to this kind of problem that would be appreciated, although I had a difficult time finding particular resources on my own.