Update a table with data from another table

Question

I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. Nothing I have tried has worked. Any thoughts?

Table1

  user_id|contact_email |contact_name
 =======================================
 123     |[email protected] |deft
 124     |[email protected]|deft3

Table2 (User table)

 user_login|user_email     |display_name
 =======================================
 123       |[email protected]  |deft
 124       |[email protected] |deft3

I have tried:

        UPDATE Table1
        SET contact_email = (SELECT Table2.user_email
                 FROM Table2
                 WHERE Table2.user_login = user_id )
        WHERE EXISTS (SELECT Table2.user_email
                 FROM Table2
                 WHERE Table2.user_login = user_id );

Answer 1

You can use next syntax:

update tbl1
join   tbl2
on     tbl1.user_id = tbl2.user_login
set    tbl1.contact_email = tbl2.user_email;

select * from tbl1;

user_id | contact_email  | contact_name
------: | :------------- | :-----------
    123 | [email protected]  | deft        
    124 | [email protected] | deft3       

dbfiddle here

Answer 2

This should do it. Update your table inner join the second table based on ids. Then we set the email from one table to the other where the ids are the same. Hope it helps.

UPDATE tbl1
INNER JOIN tbl2 ON tbl2.user_login = tbl1.user_id
SET tbl1.contact_email = tbl2.user_email;