SQL: Understanding Correlated Queries

Question

I am working with Netezza SQL.

I have the following table (last year value for each name is 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);

My Question: For these rows, I am trying to replace missing year values with the "logical year" (i.e. year + 1). The final result would look something like this:

  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

I tried the following approaches for this problem:

#approach 1 : https://stackoverflow.com/questions/75835389/sql-replacing-the-last-year-for-each-name

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

# approach 2: https://stackoverflow.com/questions/75835389/sql-replacing-the-last-year-for-each-name

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;

Problem : However, when I try these approaches within Netezza, I get the following error:

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

I tried learning more about this error by consulting the following references Sub-Queries in Netezza - based on these references, I tried to re-write the query but it still does not work:

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;

Can someone please show me how to re-write my query so I avoid this error? Is this because the UPDATE/SET statement does not allow for correlated queries?

Thanks!

if you want o use a subquery take my last answer – nbk Mar 24 '23 at 19:52 — nbk, Mar 24 '23 at 19:52

score 1 · Answer 1 · answered Mar 24 '23 at 20:17

You can select the missing rows first via

SELECT name, MAX(year) AS year FROM my_table GROUP BY name

Then you can join that to your table and do an UPDATE to get the max year per name, and then add 1 to it:

UPDATE my_table t JOIN (
  SELECT name, MAX(year) AS year FROM my_table GROUP BY name
) AS max_years
SET t.year = max_years.year + 1
WHERE t.year IS NULL;

Query #1

SELECT * FROM my_table;

id	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	2014

View on DB Fiddle

score 0 · Accepted Answer · answered Mar 25 '23 at 01:14

A possible answer - avoid the UPDATE/SET statements altogether and create a new table:

CREATE TABLE my_new_table AS (
    SELECT
        my_table.name,
        COALESCE(my_table.year, max_years.max_year + 1) AS year
    FROM
        my_table
        LEFT JOIN (
            SELECT name, MAX(year) AS max_year
            FROM my_table
            WHERE year IS NOT NULL
            GROUP BY name
        ) max_years
        ON my_table.name = max_years.name
);

Am I correct?

SQL: Understanding Correlated Queries

2 Answers2