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
