I have a DB2 LUW 9.7 table that looks something like this (before update):
Id SubId Name New_Flag Dttm
-----------------------------------------------------------------------
1 2 Sam 0 5/31/2017 1:30:00.000000 PM
2 3 Joe 1 4/25/2018 12:30:00.000000 PM
3 4 Ann 1 4/3/2018 2:10:00.000000 PM
4 5 Tim 1 4/3/2018 2:15:00.000000 PM
5 6 Tom 0 3/6/2017 2:00:00.000000 PM
6 7 Art 1 4/3/2018 2:15:00.000000 PM
7 8 Jen 1 4/25/2018 12:30:00.000000 PM
8 9 Jim 1 4/3/2018 2:10:00.000000 PM
....many more records where New_Flag = 0
So, I update the New_Flag column to equal 1 for ID #1 and #5 and set the timestamp column, Dttm, to 8/3/2018 8:30:00.000000 AM. Now the table looks like this:
Id SubId Name New_Flag Dttm
-----------------------------------------------------------------------
1 2 Sam 1 8/3/2018 8:30:00.000000 AM
2 3 Joe 1 4/25/2018 12:30:00.000000 PM
3 4 Ann 1 4/3/2018 2:10:00.000000 PM
4 5 Tim 1 4/3/2018 2:15:00.000000 PM
5 6 Tom 1 8/3/2018 8:30:00.000000 AM
6 7 Art 1 4/3/2018 2:15:00.000000 PM
7 8 Jen 1 4/25/2018 12:30:00.000000 PM
8 9 Jim 1 4/3/2018 2:10:00.000000 PM
....many more records where New_Flag = 0
I want to write an update query that will set the New_Flag column equal to 0 for all columns in the table except for the 5 with the most recent dates: ID #1, #5, #2, #7, and either #4 or #6. It doesn't matter if #4 or #6 is selected as the 5th record, so long as only 5 records are returned.
This is what the table should end up looking like (I arbitrarily chose ID #6 as one of the records that has New_Flag set to 0):
Id SubId Name New_Flag Dttm
-----------------------------------------------------------------------
1 2 Sam 1 8/3/2018 8:30:00.000000 AM
2 3 Joe 1 4/25/2018 12:30:00.000000 PM
3 4 Ann 0 4/3/2018 2:10:00.000000 PM
4 5 Tim 1 4/3/2018 2:15:00.000000 PM
5 6 Tom 1 8/3/2018 8:30:00.000000 AM
6 7 Art 0 4/3/2018 2:15:00.000000 PM
7 8 Jen 1 4/25/2018 12:30:00.000000 PM
8 9 Jim 0 4/3/2018 2:10:00.000000 PM
....many more records where New_Flag = 0
I wrote the following that gets the 5 records, but I'm having trouble converting it to an UPDATE query that will set the New_Flag column equal to 0 for every row except these 5 records:
select distinct
name,
older
from
(
select
t1.name,
t1.dttm as older
from
myTable t1
left outer join
myTable y1 on
y1.new_flag = t1.new_flag
and y1.dttm < t1.dttm
where
t1.new_flag = 1
order by
2 desc
)
fetch first 5 rows only
;
Is it possible to do this in an UPDATE query (preferably not in a stored procedure)? Is there a more efficient way to achieve what I'm trying to accomplish?
Thanks.
While Gordon's answer wasn't quite on the mark, he did help set me on the right path.
Here's the solution I came up with:
update myTable t1
set new_flag = 0
where not exists (
select
1
from
(
select
st2.*,
row_number() over (order by st2.dttm desc) as seqnum
from
myTable st2
where
new_flag = 1
) t2
where
seqnum <= 5
and t1.name = t2.name
);
This should ensure that if a record has a New_Flag = 0 and a Dttm that is more recent than one of the top 5 most recent records, that it will be ignored.