0

This question is an exact duplicate of:

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? I receive truncated incorrect double value mysql when I run the

Table1

  user_id|contact_email |contact_name
 =======================================
 123     |test@test.com |deft
 124     |test3@test.com|deft3



Table2 (User table)

 user_login|user_email     |display_name
 =======================================
 123       |test@test.com  |deft
 124       |test3@test.com |deft3

I have tried:

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

And received truncated incorrect double value mysql

and when I try:

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

I get 2 queries successfully executed. 0 record(s) were affected. There should be around 2000 records affected, so I know its not right.

marked as duplicate by ypercubeᵀᴹ mysql Mar 30 '18 at 23:47

This question was marked as an exact duplicate of an existing question.

  • 1
    Please don't repost the same question; check this link to get your two accounts merged. – RDFozz Mar 30 '18 at 19:35
  • 1
    It may help to include your table definitions; could user_id and user_login be different data types? Also - If you ran the UPDATE query twice, got an error the first time, and no error the second, then something odd is going on. How many rows did the SELECT return? – RDFozz Mar 30 '18 at 19:38
  • Sorry, I was not registered for the first question.. – Deft Tech Mar 30 '18 at 19:41
  • Select returned 2 queries successfully executed. 0 record(s) were affected. – Deft Tech Mar 30 '18 at 19:42
  • No rows returned by the SELECT? then, are you sure there are any rows in tbl1? – RDFozz Mar 30 '18 at 19:45
0

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 ;
  • Thnx for editing. I removed the redundant WHERE as well (it was the same condition as the ON). – ypercubeᵀᴹ Mar 31 '18 at 9:18
  • Hi Thanks, With this one, I get Truncated incorrect DOUBLE value: – Deft Tech Apr 1 '18 at 0:44
  • Would this be because it needs to replace the contact_email value for multiple records? – Deft Tech Apr 1 '18 at 0:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.