How to make such an UPDATE?

Question

MySQL

In general, there are records in the database, each record has a dt field containing the Timestamp of the time when the record was added.
Approximate view:
id | user | data | dt
For each user records in “bunch”, i.e. For example, there are 10 entries in the interval from 20 seconds to 20 minutes, then the whole day is empty - the next ones again around this time. Those. the interval between "heaps" is not less than 23 hours.
It is necessary to add one more column to the table - dt_start. And in it, inside each “heap”, you need to put down the _minimal_ Timestamp from this heap.
Tell me how to do it

Answer 1

if you understand the problem correctly, then you can solve it using UPDATE + JOIN, like this:

UPDATE mytable
RIGHT JOIN 
(SELECT 
   `user`, 
   MIN(dt) as dt_start, 
   DATE(dt) as d 
FROM mytable 
GROUP BY user, d) as temp
ON mytable.user = temp.user AND DATE(mytable.dt) = temp.d
SET mytable.dt_start = temp.dt_start

but in general, for each heap, putting down the minimum time value is not a good idea - you will have duplication of data, because each entry in the heap will have the same dt_start. If you have heaps so clearly looming, you should make a separate table for their list, and already put down the minimum time there