Code to copy data from one field to another.

[Jinovich]

So recently I accidently executed some sql code on my db changing 600 of my users email address to "abx@xyz.com" which has messed up a few things to say the least.

Now I can't install a backup of my forums as the incident occured about a month ago, instead I have found a backup close to the time before I corrupted the user table with the original email addresses in.

I was wondering if anyone could help write some code that would,

1) Query the active user table for users with the email address "abc@xyz.com"
2) Use the user id's from the previous query to look up the original email address from the user table from the backup.
3) Copy the email address from the backup user table into the currently active user table

Doesn't have to be fancy and plugin with vB I will settle for a simple script which connects directly to the db.

Would really appreciate if someone can help me do this.

[Marco van Herwaarden]

The following query should correc this. Replace 'userbackup' with the tablename you restored the old user table in.

UPDATE user AS u1, userbackup AS u2
SET u1.email = u2.email
WHERE u1.email = 'abx@xyz.com'
AND u1.userid = u2.userid;

Not tested! Make sure to backup your database before running manual queries.

[Jinovich]

Thanks for this Marco, I have also been suggested this,
UPDATE
user
LEFT JOIN
user2
ON
user.userid = user2.userid
SET
user.email = user2.email

This is obviously missing the conditionals that I would have to apply to my db as its been left so long (otherwise it will revert legit users who changed their email in the last month to their old email) but it would work for anyone else.

[Marco van Herwaarden]

That would (with conditional) also work, depending on your MySQL version.