基于符合条件的1条记录更新多条记录

Question

我正在尝试从数据集中选择所有记录，其中1条记录符合条件。我有一张数据表，其中包含一个人每天的工作时间。如果某个人在该周内的一条记录的"logged"标志设置为"N"，则我需要选择该周的所有记录。下面是一个数据示例：

t_hours:

Name  Week  Weekday  Hours  Logged
===============================
Jim   1     Mon      8       Y
Jim   1     Wed      8       Y
Jim   1     Fri      8       Y
Jim   2     Mon      8       Y
Jim   2     Wed      8       Y
Bill  1     Mon      8       N
Bill  1     Tue      8       Y
Bill  1     Wed      8       Y
Bill  1     Thu      8       Y
Bill  2     Mon      8       Y
Bill  2     Tue      8       Y

我想写一个查询，如果有一天Logged='N'，它会将一个人工作周的所有记录更新为Logged='N+。但我甚至不知道如何选择记录。以下是我想更新的记录：

Name  Week  Weekday  Hours  Logged
===============================
Bill  1     Mon      8       N
Bill  1     Tue      8       Y
Bill  1     Wed      8       Y
Bill  1     Thu      8       Y

我尝试了一个普通的选择，但不知道如何在where子句中有两个相关的子查询：

SELECT * FROM t_hours
WHERE (Name = (SELECT t1.Name FROM t_hours t1 
where t1.Name = t2.Name and t1.Week = t2.Week and 
t1.Logged = 'N') and
Week = (SELECT t2.Week FROM t_hours t2 
where t1.Name = t2.Name and t1.Week = t2.Week and 
t2.Logged = 'N')

但这不起作用，我们非常感谢您的帮助。

Answer 1

您可以使用可更新的CTE:

with toupdate as (
select t.*,
min(logged) over (partition by name, week) as min_logged
from t
)
update toupdate
set logged = min_logged
where min_logged = 'N' and min_logged <> logged;

如果某个人/周的logged值中的任何一个为'N'(如果有的话，其余值为'Y'(，则min(logged)表达式将返回'N'。

Answer 2

返回数据：

SELECT *
FROM   t_hours t1
WHERE  EXISTS (SELECT 1
FROM   t_hours t2
WHERE  t1.NAME = t2.NAME
AND t1.week = t2.week
AND t2.logged = 'N');  

更新数据：

UPDATE t_hours
SET    logged = 'N'
WHERE  EXISTS (SELECT 1
FROM   t_hours t2
WHERE  t1.NAME = t2.NAME
AND t1.week = t2.week
AND t2.logged = 'N');  

如果数据集足够大，可以按名称、周和日志类型进行分组。

Answer 3

使用现有进行如下尝试

select t1.* from table_name t1
where exists( select 1 from table_name t2 where t1.week=t2.week
and t2.logged='N' and t1.name=t2.name
)

Answer 4

这就是您想要的吗？

Update table set Logged='N' where
Exists (Select 1 from(Select 
Name,Workweek group by Name, 
Workweek having count(Logged='N')
>=1)t1 where Name=t1.name and  
Workweek=t1.Workweek)