Netezza SQL中替换NULL年份为逻辑年份的查询改写求助

问题场景 #

现有表结构及数据如下：

CREATE TABLE MY_TABLE
(
    name VARCHAR(50),
    year INTEGER
);
    
INSERT INTO my_table (name, year)
VALUES ('aaa', 2010);
INSERT INTO my_table (name, year)
VALUES ('aaa', 2011);
INSERT INTO my_table (name, year)
VALUES ('aaa', 2012);
INSERT INTO my_table (name, year)
VALUES ('aaa', 2013);
INSERT INTO my_table (name, year)
VALUES ('aaa', NULL);

INSERT INTO my_table (name, year)
VALUES ('bbb', 2000);
INSERT INTO my_table (name, year)
VALUES ('bbb', 2001);
INSERT INTO my_table (name, year)
VALUES ('bbb', 2002);
INSERT INTO my_table (name, year)
VALUES ('bbb', 2003);
INSERT INTO my_table (name, year)
VALUES ('bbb', NULL);

需求是将每个name对应的最后一条NULL的year替换为该name的最大非空year值+1，最终结果如下：

name year
1   aaa 2010
2   aaa 2011
3   aaa 2012
4   aaa 2013
5   aaa 2014
6   bbb 2000
7   bbb 2001
8   bbb 2002
9   bbb 2003
10  bbb 2004

原尝试方法及报错 #

尝试了两种关联子查询的UPDATE写法，均触发报错：

-- 方法1
UPDATE my_table
SET year = (SELECT MAX(year) + 1 FROM my_table t2 WHERE t2.name = my_table.name)
WHERE year IS NULL;

-- 方法2
UPDATE my_table t1
SET
    t1.Year = (
        select
            max(t2.year)
        from
            my_table t2
        where
            t2.name = t1.name
            and t2.year is not null
    ) + 1
WHERE
    Year IS NULL;

报错信息：

ERROR: this form of correlated query is not supported consider rewriting

后续尝试CTE写法也无法运行：

WITH cte AS (
    SELECT name, MAX(year) + 1 AS logical_year
    FROM my_table
    GROUP BY name
)
UPDATE my_table
SET year = cte.logical_year
FROM cte
WHERE my_table.name = cte.name AND my_table.year IS NULL;

可行解决方案 #

Netezza对UPDATE语句的关联查询支持有限，不能直接使用关联子查询或CTE关联更新，可通过以下两种方式规避：

方式1：临时表中转逻辑年份 #

先创建临时表存储每个name对应的逻辑年份，再基于临时表执行更新：

-- 创建临时表存储逻辑年份
CREATE TEMP TABLE name_logical_year AS
SELECT name, MAX(year) + 1 AS logical_year
FROM my_table
WHERE year IS NOT NULL
GROUP BY name;

-- 执行更新
UPDATE my_table
SET year = nly.logical_year
FROM name_logical_year nly
WHERE my_table.name = nly.name 
  AND my_table.year IS NULL;

方式2：直接嵌入子查询到FROM子句 #

无需临时表，将逻辑年份计算直接作为UPDATE的关联数据源：

UPDATE my_table
SET year = sub.logical_year
FROM (
    SELECT name, MAX(year) + 1 AS logical_year
    FROM my_table
    WHERE year IS NOT NULL
    GROUP BY name
) sub
WHERE my_table.name = sub.name 
  AND my_table.year IS NULL;

说明 #

Netezza的UPDATE语法允许使用FROM子句，但要求关联的数据源是独立的查询结果或临时表，而非直接的关联子查询。上述两种写法都预先计算好每个name的逻辑年份，再进行关联更新，规避了Netezza不支持的语法形式。

内容的提问来源于stack exchange，提问作者stats_noob