SQL 使用同一个表中的值更新行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13504406/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UPDATE rows with values from the same table
提问by Farhad Irani
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:
我想复制最后 3 个值，最后我的表将如下所示：
+------+-------+
|ID | value |
+------+-------+
| 1 | 150 |
| 2 | 530 |
| 3 | 950 |
| 4 | 651 |
| 5 | 530 |
| 6 | 950 |
| 7 | 651 |
+-------+------+
Is it possible?
是否可以？
回答by Erwin Brandstetter
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, 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:
如果ID 空间中存在间隙，请使用 windows 函数row_number()获取可使用的无间隙 ID。我在 CTE 中这样做，因为我将重复使用该表两次以进行自联接：
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;
You need PostgreSQL 9.1 or later for data-modifying CTEs.
您需要 PostgreSQL 9.1 或更高版本来修改数据 CTE。
回答by Edmund
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.
问题是，这是一个仅适用于该确切数据的临时更新，还是您要为该表中的所有可能数据实施的一般更新规则的情况？如果是这样，那么我们需要更多细节。
回答by Glenn
The hard-coded 3appears twice and would be replaced by however many rows you want. It assumes the last 3records actually have values. It takes those values and applies them in sequence to the set of records with null values.
硬编码3出现两次，将被替换为您想要的任意多行。它假设最后的3记录实际上有值。它采用这些值并将它们按顺序应用于具有空值的记录集。
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
