Greg H Greg H - 1 year ago 25
MySQL Question

Copy data into another table plus a linking table to maintain relationship

I have a similar problem that was previously solved here. However, there is one key difference. I need to keep the relationships to the original data.

For example, Imagine there is a table with information such as this:

user {id, first, last, address}


I'd like to move the address portion out of the 'user' table and into its own 'address' table.

user {id, first, last}
address {id, address}
user_address {user_id, address_id}


so the new 'address' table would contain the data that used to be stored in the 'user' table and the 'user_address' link table would maintain the connection to the original user.

Answer Source

In order to accomplish desired, you should apply three migration scripts.

INSERT INTO user(id, first, last)
SELECT
  u.id,
  u.first,
  u.last
FROM
  old_user u

 

INSERT INTO address(id, address)
SELECT DISTINCT
  /* I assume, that "id" is populated automatically (by increment etc.)  */
  u.address
FROM
  old_user u

 

INSERT INTO user_address(user_id, address_id)
SELECT
  u.id,
  a.id
FROM
  old_user u
JOIN
  address a ON a.address = u.address
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download