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.
Departments (PK Dept_ID)
Events (PK Event_ID, FK Dept_ID)
Shifts (PK Shift_ID, FK Event_ID, FK Dept_ID)
departments (PK id)
events (PK id, FK department_id)
shifts (PK id, FK event_id)
INSERT INTO newDB.shifts (event_id, start, end, notes)
(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;
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.
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;