用于将列值设置为与下一行的列值相同的 SQL [英] SQL for setting column value same as next row's column value
问题描述
我有一张这样的桌子
--------------------------
id some_column
--------------------------
1 null
2 abc
3 null
4 null
5 def
6 ghi
在我的场景中,表中的一些初始行在 some_column 中为空.所有最新的行都正确设置了 some_column 值.
In my scenario, some initial rows in the table have null in some_column. All the latest rows have some_column value properly set.
我想运行一个查询,它可以通过从下一个非空值复制值来替换 some_column 的所有空值.
I want to run a query which can replace all the null values of some_column by copying value from next not null value.
比如运行查询后,表会变成
For example, after running the query, table will become
--------------------------
id some_column
--------------------------
1 abc
2 abc
3 def
4 def
5 def
6 ghi
我正在尝试这样的事情
update table as t1 set some_column = (select some_column from table as t2 where t2.id > t1.id and t2.some_column is not null limit 1) where some_column is null
但出现错误
You can't specify target table 't1' for update in FROM clause
推荐答案
你可以用 JOIN
来做到:
UPDATE t AS t0
JOIN (
SELECT t1.id, MIN(t2.id) new_id
FROM t AS t1
JOIN t AS t2 ON t1.id < t2.id
WHERE t1.some_column IS NULL
AND t2.some_column IS NOT NULL
GROUP BY t1.id) AS tnew ON t0.id = tnew.id
JOIN t AS t3 ON t3.id = tnew.new_id
SET t0.some_column = t3.some_column
tnew
子查询将每个具有空值的 ID 与具有非空值的次高 ID 进行匹配.然后我将它与原始表连接两次,以配对要更新的行具有替换值的行中的值.
The tnew
subquery matches up each ID with a null value with the next highest ID with a non-null value. I then join this twice with the original table, to pair up the row to be updated with
the value from the row with the replacement value.
这篇关于用于将列值设置为与下一行的列值相同的 SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!