UPDATE rows with values from the same table

Question

I have a table like this:

+------+-------+
|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?

Erwin Brandstetter · Accepted Answer · 2022-03-16T04:14:22.223

9

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.

edited Mar 16 '22 at 04:14

answered Nov 22 '12 at 01:26

Erwin Brandstetter

605,456
145
1,078
1,228

You do not need PostgreSQL 9.1 or later to run `WITH x AS ( SELECT...) UPDATE mytable SET ... FROM x` . Data-modifying CTEs actually mean `UPDATE INSERT DELETE` in the `WITH` part. Something like `WITH x as (DELETE FROM t1 ... RETURNING t1.id) INSERT INTO t2 SELECT x.id FROM x` – Ihor Romanchenko Nov 22 '12 at 10:10
@IgorRomanchenko: Your statement is incorrect, my answer is correct. Well, the term "data-modifying CTE" seems a bit misleading, since the CTE itself does not modify data. The key statement in the manual is: `The sub-statements in WITH are executed concurrently with each other and with the main query.` Either way, you need PostgreSQL 9.1 to use any data-modifying command in direct connection with a CTE. Consider this demo: sqlfiddle.com/#!11/53063/1. – Erwin Brandstetter Nov 22 '12 at 12:37
Sorry. I'm using both 9.2 and 8.4 in production and was confused. + I'v read the manual. In "7.8.2. Data-Modifying Statements in WITH" there is always `UPDATE INSERT DELETE` in `WITH` part, so i thought it is about data modification in the CTE itself (`WITH` part). – Ihor Romanchenko Nov 22 '12 at 12:37

score 0 · Answer 2 · answered Nov 22 '12 at 01:00

0

For an ad-hoc update like this, there probably isn't going to be a better way than three simple update statements:

UPDATE mytable SET value = 530 WHERE id = 2;
UPDATE mytable SET value = 950 WHERE id = 3;
UPDATE mytable SET value = 651 WHERE id = 4;

The question is, is this an ad-hoc update that only applies to this exact data, or a case of a general update rule that you want to implement for all possible data in that table? If so, then we need more detail.

answered Nov 22 '12 at 01:00

Edmund

10,533
3
39
57

no the query has more than 10000 rows. I can't do it this way – Farhad Irani Nov 22 '12 at 01:03
So... we need more information. You said you want to copy the last 3 values. Is it always the values from the last three rows ordered by `id`? Are you always copying them onto rows with empty values? How does each row get assigned one of the last 3 values? – Edmund Nov 22 '12 at 01:07
no it is not 3. at least 2000 records. yes they're ordered by id. yes i'm copying to rows with empty values. – Farhad Irani Nov 22 '12 at 01:15

score 0 · Answer 3 · answered Nov 22 '12 at 01:50

The hard-coded 3 appears twice and would be replaced by however many rows you want. It assumes the last 3 records actually have values. It takes those values and applies them in sequence to the set of records with null values.

update a
  set value = x.value
  from (

        select nullRows.id, lastRows.value

          from ( select id, value
                       ,(row_number() over(order by id) - 1) % 3 + 1 AS key
                   from ( select id, value
                            from a
                            order by id desc
                            limit 3
                        ) x
                   order by 1

               ) AS lastRows

              ,( select id
                       ,(row_number() over(order by id) - 1) % 3 + 1 AS key
                   from a
                   where value is null
                   order by id

               ) AS nullRows

         where lastRows.key = nullRows.key

      ) x

where a.id = x.id

UPDATE rows with values from the same table

3 Answers3

Linked