如何用最新日期更新除最后5行之外的所有行的标志

Question

我有一个DB2 LUW 9.7表，它看起来像这样(在更新之前(：

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

因此，我将ID#1和#5的New_Flag列更新为等于1，并将时间戳列Dttm设置为8/3/2018 8:30:000000AM

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

我想编写一个更新查询，将表中除ID为#1、#5、#2、#7以及#4或#6的5列之外的所有列的New_Flag列设置为0。选择#4或#6作为第5条记录并不重要，只要只返回5条记录即可。

这就是表最终的样子(我任意选择ID#6作为New_Flag设置为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

我写了以下内容来获取5条记录，但我在将其转换为UPDATE查询时遇到了问题，该查询将为除这5条记录之外的每一行设置New_Flag列等于0：

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
;

是否可以在UPDATE查询中执行此操作(最好不要在存储过程中执行(？有没有更有效的方法来实现我想要实现的目标？

谢谢。

Answer 1

虽然戈登的回答不太准确，但他确实帮助我走上了正确的道路。

这是我想出的解决方案：

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
);

这应确保，如果一条记录的New_Flag=0且Dttm比前5条最新记录中的一条更新，则该记录将被忽略。

Answer 2

我认为您可以使用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
);

编辑：

如果以上内容在您的版本中不起作用，也许可以：

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
);

Answer 3

这将适用于最新版本的Db2 LUW，可以说是实现您想要的的最整洁的方法

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