Posted on: 2007/5/1 18:46
MySQL Experts read this...how to restore from a 'find and replace' tradgedy??
I've got a rather major problem. I tried to save some time by using a MySQL statement to 'Find and Replace' a load of e-mail values because I had to update about 20 users with a different address and did not want to do it one at a time.
I needed to change : firstname.lastname@example.org@mysite.com
I read here
that the best way to do that is like this :
update TABLE set COLUMN = replace(COLUMN, OLD_VALUE, NEW_VALUE);
So I entered this into the SQL area of phpMyAdmin :
update databaseprefix_users set email = replace(email, @mysite.com, @myothersite.com);
Assuming it would find all the e-mail addresses that ended in @mysite.com and replace them with @myothersite.com. But no. It's wiped out every single e-mail address in my database!!! Now none of my users have an e-mail address associated with their account!!!
Q1) What went wrong?
Q2) How can I easily restore these values from my SQL backup? I don't want to risk trying to restore the entire database in case of further damage. All I want to do is restore the ..._users table, more specifically, the e-mail column of it and repopulate them with the e-mail addresses in the backup.
Any thoughts? I am rather desperate as this makes me look like a total idiot.
Thanks a lot