How to Update SQL Query's Result in a separate column

Question

I have used following PostgreSQL query to find the time difference between end time and next start time with different source IDs.

SELECT obu_id, trip_id, start_time, end_time, dwell_time
     , end_time - LEAD(start_time) OVER(PARTITION BY obu_id ORDER BY start_time) AS diff 
 FROM clean_trips_byobu;

What I am trying to do is update the column dwell_time with the results being shown in diff(temporary result, Its not being stored anywhere) and to insert a value "-1" as instead of empty space. Please refer to the following screenshot.

Desired result:

obu_id trip_id  start_time            end_time              dwell_time  diff
2      135736   2004-10-29 15:22:14   2004-10-29 16:13:37   -02:01:42   -02:01:42
2      135738   2004-10-29 18:15:19   2004-10-29 18:28:37   -1
3      137826   2005-03-17 17:56:41   2005-03-17 18:02:31   -00:05:30   -00:05:30 
3      137826   2005-03-17 18:08:01   2005-03-17 18:12:10   -00:07:51   -00:07:51
3      137826   2005-03-17 18:20:01   2005-03-17 18:27:51   -14:51:24   -14:51:24

Answer 1

My educated guess what you might be after:

UPDATE clean_trips_byobu c
SET    dwell_time = COALESCE(u.diff, interval '-1 hour')
FROM  (
   SELECT id, ref, start_time, end_time
        , end_time - lead(start_time) OVER (PARTITION BY id ORDER BY start_time) AS diff
   FROM   clean_trips_byobu
   ) u
WHERE (c.id, c.ref, c.start_time, c.end_time)
    = (u.id, u.ref, u.start_time, u.end_time)
AND c.dwell_time IS DISTINCT FROM COALESCE(u.diff, interval '-1 hour');

• Use COALESCE() to replace NULL with a default interval.
• Use the query as subquery in your UPDATE. Join on a unique set of columns.
• If you already have values in diff, add the last line to prevent empty updates.

You should have a simpler way to identify rows than the combination of 4 columns. I suggest a surrogate primary key. Can be done with this single line of code:

ALTER TABLE clean_trips_byobu ADD COLUMN ctb_id serial PRIMARY KEY;

See:

• Do I need a primary key for my table, which has a UNIQUE (composite 4-columns), one of which can be NULL?