Update MySQL table with another table's data

Question

I read this and this, but I need to make a GROUP BY query to set another table.

logintable is like this:

id | name    | login_date
------------------------
1  | michael | 2013-01-04 
2  | michael | 2013-01-08 
3  | mary    | 2013-01-11 
4  | john    | 2013-01-15 
5  | michael | 2013-01-19 
6  | mary    | 2013-01-22 
7  | john    | 2013-01-26

I make a query like this:

SELECT * FROM logintable GROUP BY name ORDER BY id ASC

This gives me first login date of the users:

1  | michael | 2013-01-04 
3  | mary    | 2013-01-11 
4  | john    | 2013-01-15

I have another table called userstable like this:

id | name    | last_seen_date | first_login_date
------------------------------------------------
1  | michael | 2013-02-02     |  
2  | john    | 2013-02-04     |   
3  | mary    | 2013-02-16     |

I need to update userstable's first_login_date column, with the first result. How can I do this ? (I have 75k records in logintable and 10k records in userstable)

You mean like INSERT...SELECT, except for UPDATE? You should just UPDATE with a JOIN or two. — Jeremy Holovacs, Apr 12 '13 at 19:01
This is not guaranteed to give you the first login date. Use `MIN(login_date)` instead. — Bart Friederichs, Apr 12 '13 at 19:02

score 15 · Accepted Answer · answered Apr 12 '13 at 19:02

UPDATE  userstable a
        INNER JOIN
        (
            SELECT  name, min(login_date) min_date
            FROM    logintable
            GROUP   BY name
        ) b ON a.name = b.Name
SET     a.first_login_table = b.min_date

for faster performance, you need to add an INDEX on column Name for both tables. This will prevent from performing full table scan which is very slow in large databases.

Adding INDEX on table userstable:

ALTER TABLE usersTable ADD INDEX (Name);

for referential integrity, make table logintable dependent on table userstable by defining FOREIGN KEY constraint:

ALTER TABLE loginTable ADD CONSTRAINT tb_fk
FOREIGN KEY (Name) REFRENCES usersTable (Name)

One more thing please. What if I want to set "first_login_table" column if column record is NULL. Can I add "WHERE a.first_login_table NOT NULL" in the end of the query? — trante, Apr 12 '13 at 20:01
add `WHERE a.first_login_table IS NULL` to set only if the column is null — John Woo, Apr 13 '13 at 06:05

score 4 · Answer 2 · answered Apr 12 '13 at 19:02

4

UPDATE userstable AS u
INNER JOIN
(
   SELECT MIN(login_date) AS MinLoginDate, Name
   FROM logintable 
   GROUP BY name
) AS l ON u.name = l.name
SET u.first_login_date = l.MinLoginDate

answered Apr 12 '13 at 19:02

Mahmoud Gamal

78,257
17
139
164

MikeTWebb · Answer 3 · 2013-04-12T19:14:19.203

2

I don't know MySql...but in SQL Server you would write something like this:

UPDATE userstable set first_login_date = (SELECT MIN(login_date) FROM logintable where name = userstable.name)

edited Apr 12 '13 at 19:14

answered Apr 12 '13 at 19:09

MikeTWebb

9,149
25
93
132

score 0 · Answer 4 · answered Mar 05 '14 at 10:56

UPDATE userstable a INNER JOIN ( SELECT name, min(login_date) min_date FROM logintable GROUP BY name ) b ON a.name = b.Name SET a.first_login_table = b.min_date

This will definitely show your output.

Update MySQL table with another table's data

4 Answers4