Clash Royale CLAN TAG#URR8PPP

How to update a flag for all rows except the last 5 with the latest dates?

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.

3 Answers
3

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.

I think you can do this with fetch/offset:

fetch

offset

update mytable
set flag = 0
where dttm < (select t2.dttm
from mytable t2
order by t2.dttm desc
offset 4 fetch first 1 row only
);


EDIT:

If the above doesn't work in your version, perhaps this does:

update mytable
set flag = 0
where dttm < (select t2.dttm
from (select t2.*, row_number() over (order by t2.dttm desc) as seqnum
from mytable t2
) t2
where seqnum = 5
);


limit 4 fetch first 1 row only

limit

offset

This will work on recent versions of Db2 LUW, and is arguably the neatest way to achieve what you want

update
( select
new_flag
from
( select
new_flag
, row_number() over (order by dttm desc) as seqnum
from
myTable t
)
where
seqnum <= 5
and new_flag <> 0
)
set new_flag = 0


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.