Mysql – Update a table with data from another table

MySQLupdate

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

Best Answer

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 | test@test.com  | deft        
    124 | test3@test.com | deft3

dbfiddle here

Related Solutions

Mysql – Help optimizing MySQL slow query

I would like to get rid of "Using temporary; Using filesort"

One of the problems I see is that you're using different GROUP BY and ORDER BY clauses. From the manual on how MySQL uses temporary tables:

If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

As soon as you create a temporary table, it will need to be sorted according to your ORDER BY clause, indicated by 'using filesort'.

This execution plan at leasts uses the indexes to appropriately limit the number of rows found.

I would also look through the docs on ORDER BY optimization.

Retrieve Multiple Values from Different Tables in MySQL

Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:

INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`) 
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);

After you have done that your query should be more like the result you are after. Running this query:

select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;

returns the following result set:

user_id username    friend_id   friend  status_id   message created_at  privacy user_id is_reply    device  
1   admin   2   demo    1   dasdasdasdasdasd    2012-08-13 19:45:37 NULL    1   0   
2   demo    1   admin   2   dasdasdasdasdasd    2012-08-13 19:46:03 NULL    2   0   
1   admin   2   demo    3   dasdsad344hbvnbnhjhgjhjghjhj    2012-08-13 21:54:53 NULL    1   0

Is this anywhere near what you are looking for?