Search code examples
mysqldatabaseschemastructure

MySQL converting a tables schema while keeping the data in the new structure (best schema u ever see)

I am forking a project and want to convert one of the tables schemas to a more.. viable structure

Currently:

steamid | mapname | cp1 | cp2 | cp3 | cp4 | cp5 | cp6 | cp7 | cp8 | cp9 | cp10 | cp 11 | cp12 | cp13 | cp14 | cp15 | cp16 | cp17 | cp18 | cp19 | cp20 | cp21  | cp22 | cp23 | cp24 | cp25 | cp26 | cp27 | cp28 | cp29 | cp30 | cp31 | cp32 | cp33 | cp34 | cp35 | zonegroup

steamid is the user id, mapname is the map, the cp# columns store the players time for a specific checkpoint and the zonegroup stores which "stage" of the map

I want to change it to:

steamid | mapname | cp | time | zonegroup

Is there any realistic solution to convert this table while also keeping the data, so moving a time which was stored previously in cp1 to the new cp column which would have a value of 1, and the new time column would contain whatever was in the old cp1 column

Thanks!

Solution

  • You could transfer the data to a new table with a very long-winded INSERT ... SELECT query:

    INSERT INTO new_table (steamid, mapname, cp, time, zonegroup)
SELECT * FROM (
    SELECT steamid, mapname, 1 AS cp, cp1 AS time, zonegroup FROM old_table
    UNION ALL
    SELECT steamid, mapname, 2, cp2, zonegroup FROM old_table
    UNION ALL
    ...
    SELECT steamid, mapname, 35, cp35, zonegroup FROM old_table) v