1

I am working with Netezza SQL.

I have a table that looks like this:

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

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

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

My Question: For each set of NAMES, for rows where the YEAR is NULL - I want to replace those rows with the value of YEAR from the row directly above.

The final answer 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 SQL code:

UPDATE my_table
SET Year = LAST_VALUE(Year IGNORE NULLS) OVER (PARTITION BY NAME ORDER BY Year ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1
WHERE Year IS NULL;

But I got the following error: Cannot use window aggregates in UPDATE

Can someone please show me how to fix this? Is there another way to do this using Netezza SQL functions?

Thanks!

References:

stats_noob
  • 5,401
  • 4
  • 27
  • 83

2 Answers2

2

Unfortunately, as the error message suggests, you cannot use window functions in an UPDATE statement in Netezza SQL. However, there is another way to achieve the desired result using a subquery.

Here's the modified SQL code that should work:

UPDATE my_table t1
SET Year = (
SELECT MAX(t2.Year) + 1
FROM my_table t2
WHERE t2.Name = t1.Name AND t2.Year IS NOT NULL AND t2.Year < t1.Year
)
WHERE Year IS NULL;

This code uses a subquery to find the maximum non-null year value that is less than the current row's year value, and adds one to that value to get the new year value for the current row.

Note that this code assumes that there is always a non-null year value for each name group that comes before the first null year value. If this is not the case, you may need to modify the query accordingly.

I hope this helps!

Ole EH Dufour
  • 2,968
  • 4
  • 23
  • 48
  • @ Ole EH Dufour!: Thank you so much for your answer! When I tried your code, I got the following error: this form of correlated query is not supported - consider rewriting – stats_noob Mar 23 '23 at 14:39
  • 1
    @stats_noob Try this: UPDATE my_table t1 SET Year = ( SELECT MAX(Year) + 1 FROM my_table t2 WHERE t2.Name = t1.Name AND t2.Year IS NOT NULL AND t2.Year < t1.Year ) WHERE Year IS NULL; – Ole EH Dufour Mar 23 '23 at 15:12
  • @ Ole EH Dufour: Thank you for your reply! Unfortunately, I am still getting the same error :( – stats_noob Mar 23 '23 at 15:35
1

your error mesage already says that you can't use it directly, but you can make a subquery

UPDATE my_table
SET Year = sub.lastdate
FROM (SELECT rowid as rown,  LAST_VALUE(Year IGNORE NULLS) OVER (PARTITION BY NAME ORDER BY Year ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) + 1 as lastdate
FROM my_table) sub
WHERE Year IS NULL and rowid = sub.rown;
nbk
  • 45,398
  • 8
  • 30
  • 47