SQL: merging tables & preserving FKs
dev at archonet.com
Tue Jun 16 16:17:32 BST 2009
Paul Makepeace wrote:
> 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:
I'd create a (temporary) table with the fields you want:
CREATE TEMPORARY TABLE xfer AS
SELECT ... AS olduid, ... AS newuid FROM ...
> (I'm tempted to just spit out SQL from perl -lne but was curious if there
> was a better way.)
> MySQL 5, fwiw.
Don't know if mysql will let you do the subqueries you need, but...
UPDATE info SET uid = (SELECT newuid FROM xfer WHERE olduid = uid)
WHERE uid IN (SELECT olduid FROM xfer);
You'll want the where clause otherwise you will end up with NULLs where
there is no match to olduid.
PostgreSQL has the useful but non-standard FROM clause for updates:
UPDATE info SET uid = newuid FROM xfer WHERE uid = xfer.olduid;
More information about the london.pm