如何编写SQL查询更新另一表时间区间内的行状态？

针对需求，直接使用SQL Server的UPDATE ... JOIN语法即可实现，核心逻辑是通过id关联两张表，并匹配时间区间条件完成状态更新。

核心更新语句 #

UPDATE i
SET i.status = im.status
FROM [Items] i
INNER JOIN [ItemsMaintenance] im
    ON i.id = im.id
    AND i.[timestamp] BETWEEN im.start_timestamp AND im.stop_timestamp;

关键说明 #

• 用INNER JOIN确保仅更新存在匹配维护区间的Items行，完全符合“timestamp落在对应区间内才更新”的要求。
• 因ItemsMaintenance中每个id的时间区间无重叠，不会出现同一Items行被多次更新的冲突问题。

性能优化建议 #

由于两张表每日持续增长，为避免全表扫描拖慢执行速度，建议创建以下索引：

• 在[Items]表创建复合索引：CREATE NONCLUSTERED INDEX IX_Items_Id_Timestamp ON [Items](id, [timestamp]);
• 在[ItemsMaintenance]表创建复合索引：CREATE NONCLUSTERED INDEX IX_ItemsMaintenance_Id_TimeRange ON [ItemsMaintenance](id, start_timestamp, stop_timestamp) INCLUDE (status);

测试示例 #

假设存在以下数据：

• Items表行：id=1, timestamp='2024-05-20 10:30:00', data_value=100, status='pending'
• ItemsMaintenance表行：id=1, start_timestamp='2024-05-20 10:00:00', stop_timestamp='2024-05-20 11:00:00', status='done'

执行上述更新语句后，Items中该行的status会被更新为done，与你描述的示例一致。

内容的提问来源于stack exchange，提问作者Szymskiii