Update MySQL table with another table's data
mysql, sql, sql-update
Solution
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)
Problem
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)