SQL: merging tables & preserving FKs

Paul Makepeace 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 mailing list