Search code examples
mysqldatabasefind-replace

Update MySQL columns based on other columns

So, I have a members table that we'll call table_members. Among others, it has a column called email.

There's also a table called table_oldnewemail. It has two columns: old_email and new_email.

I'd like to:
1. Find the row in table_members where table_members.email equals table_oldnewemail.old_email
2. Then replace table_members.email with the corresponding table_oldnewemail.new_email
3. Repeat for all table_oldnewemail values

I feel I have all the necessary parts, but is this even possible with a MySQL query? What would be an otherwise smart way to automate such a process?

Solution

  • Did you try something like this:

    UPDATE table_members t INNER JOIN
       table_oldnewemail tno
       ON t.email = tno.old_email
    SET t.email = tno.new_email;