I have a table like this:
x
+------+-------+
|ID | value |
+------+-------+
| 1 | 150 |
| 2 | |
| 3 | |
| 4 | |
| 5 | 530 |
| 6 | 950 |
| 7 | 651 |
+-------+------+
I want to copy the last 3 values and at the end my table will look like this:
+------+-------+
|ID | value |
+------+-------+
| 1 | 150 |
| 2 | 530 |
| 3 | 950 |
| 4 | 651 |
| 5 | 530 |
| 6 | 950 |
| 7 | 651 |
+-------+------+
Is it possible?
Advertisement
Answer
Use a self-join:
UPDATE mytable m
SET value = m0.value
FROM mytable m0
WHERE m.id = (m0.id - 3) -- define offset
AND m.id BETWEEN 2 AND 4 -- define range to be affected
AND m.value IS NULL; -- make sure only NULL values are updated
If there are gaps in the ID space, generate gapless IDs with the window function row_number()
. I do that in a CTE, because I am going to reuse the table twice for a self-join:
WITH x AS (
SELECT *, row_number() OVER (ORDER BY ID) AS rn
FROM mytable
)
UPDATE mytable m
SET value = y.value
FROM x
JOIN x AS y ON x.rn = (y.rn - 4567) -- JOIN CTE x AS y with an offset
WHERE x.id = m.id -- JOIN CTE x to target table
AND m.id BETWEEN 1235 AND 3455
AND m.value IS NULL;
You need PostgreSQL 9.1 or later for data-modifying CTEs.