SQL: merging tables & preserving FKs

[Richard Huxton]

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;

-- 
   Richard Huxton
   Archonet Ltd