2

I am working with Netezza SQL.

I have the following table in which only the last year for each name is NULL:

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', 2012);
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', 2002);
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 2012
4   aaa 2013
5   aaa NULL
6   bbb 2000
7   bbb 2001
8   bbb 2002
9   bbb 2003
10  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

In a previous question (SQL: Using Window Aggregate Functions), I had attempted this problem in which any row for a name could have a missing year - but I was not able to solve this problem:

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;

#Error: Cannot use window aggregates in UPDATE

I was hoping that in this simpler version of the problem, there might be an easier way to solve this.

Can someone please show me how to do this?

Thanks!

stats_noob
  • 5,401
  • 4
  • 27
  • 83

3 Answers3

2

This should be possible with a simple SQL query using MAX() function and a subquery as in this DBFIDDLE

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

Update;

Based on your error : this form of correlated query is not supported consider rewriting

We can re-write this query using common table expression (CTE). Here is a demo

WITH cte_max_year AS (
  SELECT name, MAX(year) + 1 AS max_year
  FROM sample_table
  GROUP BY name
)
UPDATE sample_table
SET year = cte_max_year.max_year
FROM sample_table
INNER JOIN cte_max_year
ON sample_table.name = cte_max_year.name
WHERE sample_table.year IS NULL;

In Netezza; You can write it as :

UPDATE my_table
SET year = (SELECT MAX(year) + 1 FROM my_table t2 WHERE t2.name = my_table.name)
WHERE year IS NULL
AND EXISTS (SELECT 1 FROM my_table t2 
WHERE t2.name = my_table.name AND t2.year IS NOT NULL);
Tushar
  • 3,527
  • 9
  • 27
  • 49
1

You can use

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;
markalex
  • 8,623
  • 2
  • 7
  • 32
1

You actually don't need such complicated stuff.

Most things in SQL can work by joins only.

So, let's break the task in small parts

  1. Find last year for name
  2. Find row with no year
  3. Join them
  4. Make it to an UPDATE

so we have

 name | year
------+------
 aaa  | 2010
 aaa  | 2011
 aaa  | 2012
 aaa  | 2013
 aaa  |
 bbb  | 2000
 bbb  | 2001
 bbb  | 2002
 bbb  | 2003
 bbb  |
(10 rows)

1)find last year for name

select name, MAX(year) as year
from MY_TABLE as t1
group by name

result:

 name | max
------+------
 aaa  | 2013
 bbb  | 2003
  1. find row with no year is very basic, just where t1.year is null 3)join them I'm not sure what Netezza has, I would normally do with but we can go by nested queries
select t1.name, t1.year, t2.year + 1
from MY_TABLE as t1 
    inner join 
    (
        select name, MAX(year) as year
        from MY_TABLE as t1
        group by name   
    ) as t2 on t1.name = t2.name
where t1.year is null

will get us

 name | year | ?column?
------+------+----------
 aaa  |      |     2014
 bbb  |      |     2004

ANSWER

  1. make it to an UPDATE now make it to an update
update MY_TABLE as o
set year = t2.year + 1
from MY_TABLE as t1 inner join 
    (
        select name, MAX(year) as year
        from MY_TABLE as t1
        group by name   
    ) as t2 on t1.name = t2.name
where o.year is null and o.name = t1.name
merof
  • 121
  • 7