mysql update multiple rows based on select, based on current row value

Question

I want to update my table visitors once an hour based on the count() from another table called sessions.

The code I have so far:

UPDATE visitors
INNER JOIN sessions 
   on visitors.visitor_ID = sessions.visitor_ID
SET visitors.last_30_day_sessions = (select count(sessions.session_ID) 
                                     where sessions.session_timestamp >= NOW() - INTERVAL 30 DAY)

It seems to be doing something but the numbers don't match when I simply check the number of sessions the visitor actually made in the past 30 days.

Your inner select doesnt have FROM clause

Juan Carlos Oropeza
– Juan Carlos Oropeza

2018-07-09 14:59:46 +00:00
Commented Jul 9, 2018 at 14:59 — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Jul 9, 2018 at 14:59

Juan Carlos Oropeza · Accepted Answer · 2018-07-09 15:33:08Z

1

Use correlated query:

UPDATE visitors
SET last_30_day_sessions = ( SELECT count(session_ID) 
                             FROM sessions
                             WHERE visitors.visitor_ID = sessions.visitor_ID
                               AND sessions.session_timestamp >= NOW() - INTERVAL 30 DAY
                           )

As a bonus if you want use join you need calculate the totals first

UPDATE visitors V
JOIN (SELECT visitor_ID, count(session_ID) session_count
      FROM sessions
      WHERE session_timestamp >= NOW() - INTERVAL 30 DAY
      GROUP BY visitor_ID
     ) s
   ON v.visitor_ID = s.visitor_ID
SET v.last_30_day_sessions  = s.session_count

edited Jul 9, 2018 at 15:33

answered Jul 9, 2018 at 15:12

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql update multiple rows based on select, based on current row value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related