Update rows in table with id from row before in PostgreSQL

Question

Say I have a table like this:

| id | grp   | time_added | previous_id |
|----|-------|------------|-------------|
| 1  | 1     | 5          | null        |
| 2  | 1     | 8          | null        |
| 3  | 2     | 9          | null        |
| 4  | 1     | 12         | null        |
| 5  | 2     | 15         | null        |

(time_added is actually a date, but numbers are used here for readability)

I want to update the previous_id of each row so that it's equal to the id of the previous row in the same group grp sorted by time_added.

This means that after running the update query, the table above should look like:

| id | grp   | time_added | previous_id |
|----|-------|------------|-------------|
| 1  | 1     | 5          | null        |
| 2  | 1     | 8          | 1           |
| 3  | 2     | 9          | null        |
| 4  | 1     | 12         | 2           |
| 5  | 2     | 15         | 3           |

What I have so far is the following query:

UPDATE the_table r
SET previous_id = sub.id
FROM (
  SELECT id, time_added, grp
  FROM the_table
  ORDER BY time_added DESC
) sub
WHERE (
  r.grp = sub.grp
  AND sub.time_added < r.time_added
);

However, I suspect the ORDER BY of the subquery does not do what I want it to, and the previous_id is set to the id of a random row below it, not the one strictly below it.

See this fiddle for an example of what happens. Row 3 is given a previous_id of 1, when it should be 2.

McNets · Accepted Answer · 2019-01-16 21:47:26Z

1

You can get your desired result by using LAG() function.

select
    id,
    grp,
    time_added,
    lag(id) over (partition by grp order by grp, time_added) previous_id
from
    tbl;

You can update in this way:

with x as
(
    select
        id,
        lag(id) over (partition by grp order by grp, time_added) previous_id
    from
        tbl
)
update tbl
set    
    previous_id = x.previous_id
from
    x
where
    x.id = tbl.id;

db<>fiddle here

edited Jan 16, 2019 at 21:47

answered Jan 16, 2019 at 16:11

McNets

24k11 gold badges52 silver badges91 bronze badges

You can remove grp from order by grp, id

Lennart - Slava Ukraini
– Lennart - Slava Ukraini

2019-01-16 16:22:37 +00:00
Commented Jan 16, 2019 at 16:22
Thank you for the tip. What solved my issue was: ``` UPDATE the_table r SET previous_id = sub.p_id FROM ( SELECT id, time_added, grp, lag(id) OVER (PARTITION BY grp ORDER BY time_added) p_id FROM the_table ) sub WHERE sub.id = r.id; ```

Hans
– Hans

2019-01-16 16:28:33 +00:00
Commented Jan 16, 2019 at 16:28
@McNets, since ordering is done per grp, I don't see how ordering by grp should matter? Can you give an example where it will make a difference?

Lennart - Slava Ukraini
– Lennart - Slava Ukraini

2019-01-16 21:40:04 +00:00
Commented Jan 16, 2019 at 21:40
@Lennart sorry, you're right. In this case order is set by time_added, may be I misread the question.

McNets
– McNets

2019-01-16 21:47:06 +00:00
Commented Jan 16, 2019 at 21:47

Add a comment |

Stack Exchange Network

Update rows in table with id from row before in PostgreSQL

1 Answer 1

Your Answer

Hot Network Questions

Update rows in table with id from row before in PostgreSQL

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions