Manish Manish - 2 years ago 71
MySQL Question

Select & update in same query

I have two tables user & user login history. I need to make a report of the times a particular user is logging into the system. The table contains millions of rows of data. So running a nested query to fetch number of logins of users is taking a lot of time.

I am trying to loop through all the users and update the logins column. How can I do this in one query?

The schema is like this:


  • id INT(10)

  • username VARCHAR(7)

  • logins INT(10)


  • id INT(10)

  • userid INT(10)

  • login_date DATETIME(19)!9/dc4149

I'm running this query

UPDATE users u
SET u.logins = (SELECT COUNT(*)
FROM user_logs
WHERE userid =

This is not working.

Is there any way how I could loop through users & update their respective login count?

I tried doing this with PHP but as the tables are very large. Doing this 1 by 1 takes very time.

Can I do this via command line?

Answer Source

An update should take so long, especially if you have proper indexed on both tables.

Try this:

UPDATE users u
INNER JOIN(SELECT ul.userid,count(1) as cnt FROM user_logs ul GROUP BY ul.userid) u2
 ON(u2.userid =
SET u.logins = u2.cnt

Then make sure you have the following indexes:

users - (id,logins)
user_logins - (userid)

If that doesn't help - try doing this in two steps , build a derived table with the sub query results, and update by it :

CREATE TABLE temp_for_update AS(
SELECT ul.userid,count(1) as cnt
FROM user_logs ul 
GROUP BY ul.userid);

ON temp_for_update (userid,cnt);

UPDATE users u
INNER JOIN temp_for_update u2
 ON(u2.userid =
SET u.logins = u2.cnt

This should defiantly be faster.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download