CODEWITHSUNDEEP
mysqlsql-servermigrationdata-migration
Jason
Jason

Reputation: 74

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!

Upvotes: 0

Views: 51

Answers (1)

Solarflare
Solarflare

Reputation: 11106

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;

Upvotes: 1

Related Questions