Jason Jason - 2 months ago 6
MySQL Question

Migrating MySQL data and looking up new FK?

I'm trying to migrate application data from one MySQL DB to another (with a different schema, of course) and I'm running into trouble when trying to maintain relationships from the old DB to the new.

Structure



The basic idea is an event management system, where different groups/departments can create events and shifts for these events.

DB Structure



I'm only showing relevant columns to keep things simple(r)

*Old DB*
Departments (PK Dept_ID)
Events (PK Event_ID, FK Dept_ID)
Shifts (PK Shift_ID, FK Event_ID, FK Dept_ID)

*New DB*
departments (PK id)
events (PK id, FK department_id)
shifts (PK id, FK event_id)


Migrating Departments and Events was pretty straightforward with
INSERT...SELECT
queries. For shifts, well... this is what I have so far:

INSERT INTO newDB.shifts (event_id, start, end, notes)
SELECT
(SELECT C.id FROM newDB.events C INNER JOIN oldDB.events E
ON E.Title = C.title AND E.StartTime = C.start_time AND E.Location = C.location),
* title, start, end combination is unique *
Start, End, Notes FROM oldDB.shifts S
WHERE S.Dept_ID = @oldDeptId;


I started at the top of the relationship chain and migrated a single department (Dept_ID is set in @oldDeptId variable), then migrated all events from this department using a similar
INSERT...SELECT
query. Now, when I copy the old shift data, I need the
newDB.shifts.event_id
value to match the updated
newDB.events.id
value which is assigned as an auto increment value to maintain the relationship.

The
INNER JOIN
subquery returns all
newDB.event.id
matched to the correct
oldDB.Event.Event_ID
as expected, however the subquery can only return one result (MySQL Error 1242).

Am I on the right track, or is there a better way to do this? Thanks!

Answer

Your inner query returns a complete table, while you are only allowed to get a single value there (that is what your error message says). If title, start, end is really unique, you can use it in a join to get your new pk, e.g.

INSERT INTO newDB.shifts (event_id, start, end, notes)
SELECT C.id, S.Start, S.End, S.Notes 
from oldDB.shifts S
join oldDB.events E
on E.Event_ID = S.Event_ID
join newDB.events C   
on E.Title = C.title AND E.StartTime = C.start_time AND E.Location = C.location;

You don't need to do it per department, you can migrate the table as a whole.

If title, start, end is not unique (or as general purpose idea for the future), you can temporarily add the old pks to the new tables, and join on these to get the new pk from the old one - because they are unique in any scenario. You can then e.g. simply use

INSERT INTO newDB.shifts (event_id, start, end, notes)
SELECT C.id, S.Start, S.End, S.Notes 
from oldDB.shifts S
join newDB.events C   
on C.old_event_id = S.event_id;