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 );

• ypercubeᵀᴹ: Your query seems correct. What problem did you face?

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

• RDFozz: FYI - OP has reposted under a registered account here; using this UPDATE statement evidently generated an error.
• McNets: @RDFozz I've seen. Why evidently?
• RDFozz: I'm assuming he had issues editing this post - this user is unregistered, the new one is registered. I suggested merging accounts.

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;