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)

Solution 1

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)

Solution 2

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

Solution 3

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)