Peter, how about if you ran a query like this:
SELECT CONCAT("UPDATE table2 SET id=", tr.newid, " WHERE id=", t2.id, ";")
FROM transfertable tr, table2 t2
WHERE tr.oldid = t2.id
if you put the above query into a file "id-update.sql", you could
perhaps even do something like this:
mysql < id-update.sql | mysql
I'd highly recommend running it against a test copy of your data
first, but I think it ought to work.
Dan
On 9/28/06, Peter Van Dijck <[EMAIL PROTECTED]> wrote:
Hi all,
I have a pretty complex query going on...
In 'transfertable' we have oldid and newid. The old id's are mapped to new id's.
In table2 we have the id.
To make things more interesting, the id isn't a primary key in table2,
the primary key consists of 3 fields...
We need to adjust table2 so that every id (which is the old id) is
replaced with the newid according to 'transfertable'.
I've been trying to do this, but I'm hitting the limits of my mysql
wizardry.. any suggestions? I don't think we can just do
update table2 set id = select newid from transfertable where oldid =
id (that wouldn't work anyways?)
I'm stuck, any help is very welcome!
Thanks!
Peter
--
Find 10000s of videoblogs and podcasts at http://mefeedia.com
my blog: http://poorbuthappy.com/ease/
my job: http://petervandijck.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
