使用同一表中的值更新行 [英] UPDATE rows with values from the same table

问题描述

我有一个这样的表：

+------+-------+
|ID    | value |
+------+-------+
| 1    | 150   |
| 2    |       |
| 3    |       |
| 4    |       |
| 5    | 530   |
| 6    | 950   |
| 7    | 651   |
+-------+------+

I要复制最后3个值，最后我的表将如下所示：

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

是

推荐答案

使用自加入：

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

如果ID空间中存在间隙，请使用Windows函数 row_number（）来获取完整的ID。我在CTE中这样做，因为我将重复使用该表两次以进行自联接：

If there are gaps in the ID space, use the windows function row_number() to get gapless ID to work with. 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 original
AND    m.id BETWEEN 1235 AND 3455
AND    m.value IS NULL;

对于修改数据的CTE 。

这篇关于使用同一表中的值更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！