Okay, so you know when you are lazy and do something you know you will have to change one day... that one day is now!
Ive got a live system running (clients logging in 24/7) that requires a database change.
Here is the query i need to run to update it:
ALTER TABLE dd_hist ADD COLUMN histobjid int(9) AFTER histobj; UPDATE dd_hist SET dd_hist.histobjid = (SELECT dd_obj.objid FROM dd_obj WHERE dd_obj.objname=dd_hist.histobj);
Basically, as you can see i've been associating a history table to objects by using a string value... now we need to be able to change the name of the object, and still associate the history (i.e. those ID numbers i should have just used in the first place!) Which is easy to do, the problem comes in that there is currently about 20 000 rows in dd_obj, and 30 000 in dd_hist. Not all of the rows in dd_hist are associated to objects, so in this case the value of dd_hist.hostobj is NULL - and in some cases the the object itself has been removed, but the history still has a value associated to it.
This query works, and just doesn't update the dd_hist.histobjid when it doesn't find a value. When i ran the query on my test system (An AMD64 3200) it took 45 mins. The live server is not as high spec - and i need to minimize the downtime as much as possible.
Can anyone see a better, quicker way to do this?
I have a bunch of other updates to do at the same time and i dont want to leave a "temporarily down for updates" page for too long.
Thanks in advance.