stack stack - 5 months ago 9
MySQL Question

How can I update all rows of a table based on the values of another table?

I have a table like this:

// requests
+----+----------------+----------------+-------------+
| id | user_id | ip | unix_time |
+----+----------------+----------------+-------------+
| 1 | 12353 | NULL | 1339412843 |
| 2 | 12353 | NULL | 1339412864 |
| 3 | NULL | 178.253.29.175 | 1339412894 |
| 4 | 3422 | NULL | 1339412899 |
| 5 | 3422 | NULL | 1339412906 |
| 6 | 3422 | NULL | 1339412906 |
| 7 | NULL | 148.23.29.109 | 1339413640 |
| 8 | NULL | 148.23.29.109 | 1339413621 |
| 9 | 5462 | NULL | 1339414490 |
| 10 | NULL | 178.253.29.175 | 1339419901 |
| 11 | 8007 | NULL | 1339424860 |
| 12 | 8007 | NULL | 1339424822 |
| 13 | 12353 | NULL | 1339424902 |
| . | . | . | . |
| . | . | . | . |
| . | . | . | . |
+----+----------------+----------------+-------------+


Also I have this table:

// per_days
+----+---------+--------------+----------------+-----------------+--------------+
| id | user_id | AllVisited | MaxConsecutive | LastConsecutive | request_numb |
+----+---------+--------------+----------------+-----------------+--------------+
| 1 | 12353 | 43 | 8 | 3 | 47 |
| 2 | 3422 | 530 | 130 | 32 | 100 |
| . | . | . | . | . | . |
| . | . | . | . | . | . |
| . | . | . | . | . | . |
+----+---------+--------------+----------------+-----------------+--------------+
-- each user has one row into this ^ table (I mean user_id column is unique)





I need a query to select all rows from
requests
table which are in last day (I'll execute that query every day by an event) and then updates all rows of
per_days
table (for each user separately). Something like this:

UPDATE
per_days AS p
JOIN requests AS r
ON p.user_id = r.user_id
SET p.AllVisited = p.AllVisited + IF( /* there is a row */, 1, 0),
p.MaxConsecutive = IF( p.LastConsecutive > p.MaxConsecutive, LastConsecutive, MaxConsecutive),
p.LastConsecutive = IF( /* there is a row */, p.LastConsecutive + 1, 1),
p.request_numb = { /* count(1) - the number of all requests (all selected rows) for specific user in this day */ }
WHERE r.unix_time > subdate(now(), interval '1' day)


How can I fix my query?

Answer

Join with a subquery that returns all the user IDs in requests in the time range. Use LEFT JOIN, and then your if there's a row test becomes r.user_id IS NOT NULL.

UPDATE 
     per_days AS p
LEFT JOIN (
    SELECT DISTINCT user_id
    FROM requests 
    WHERE unix_time > subdate(now(), interval '1' day)) AS r
ON   p.user_id = r.user_id
SET  p.AllVisited      = p.AllVisited + IF(r.user_id IS NOT NULL, 1, 0),
     p.MaxConsecutive  = IF( p.LastConsecutive > p.MaxConsecutive, LastConsecutive, MaxConsecutive),    
     p.LastConsecutive = IF(r.user_id IS NOT NULL, p.LastConsecutive + 1, 1)