Update columns based on calculation

Question

My table looks like this:

    id     entry_date          
    1      21/12/2020 15:00          
    1      21/12/2020 17:00          
    1      21/12/2020 19:00          
    2      24/12/2020 00:00         
    2      24/12/2020 12:00

I have a list of id's connected to datestamps. I can manage to calculate the difference between their latest and first entry as follows:

SELECT id, TIMESTAMPDIFF(hour, MIN(entry_date), MAX(entry_date))
FROM mytable
GROUP BY id;

However, I am unsure how I can update my table to reflect these calculations. What I want is the following:

id     entry_date          time_difference
1      21/12/2020 15:00          4
1      21/12/2020 17:00          4
1      21/12/2020 19:00          4
2      24/12/2020 00:00          12
2      24/12/2020 12:00          12

Answer 1

In MySQL, you can self-join:

update mytable t
inner join (
    select id, 
        timestampdiff(hour, min(entry_date), max(entry_date)) as time_difference
    from mytable
    group by id
) t1 on t1.id = t.id
set t.time_difference = t1.time_difference

I would not necessarily recommend storing this derived information, because it is hard to keep it up to date. Instead, you can create a view. If you are running MySQL 8.0:

create view myview as
select t.*,
    timestampdiff(
        hour, 
        min(entry_date) over(partition by id), 
        max(entry_date) over(partition by id)
    ) as timedifference
from mytable t