SQL: merging tables & preserving FKs
paulm at paulm.com
Tue Jun 16 15:42:26 BST 2009
OK SQL smarties,
I have two user tables, user and old_user, and a third table, let's say,
'info', that refers to old_user. I would like to merge/add users that are in
old_user to user and then update the info.old_user_id to their new
auto_increment'ed PKs in user.
My strategy so far is to add a temporary column to record the
old_user.idand then insert into user:
alter table user add column old_user_id int;
insert into user (old_user_id, fname, ...) select id, ... from old_user;
So I have a map of old_user.id and user.id in user. But, I'm not sure how in
SQL to update info.old_user_id. Any ideas?
(I'm tempted to just spit out SQL from perl -lne but was curious if there
was a better way.)
MySQL 5, fwiw.
More information about the london.pm