SQL技术需求：使用NOT IN子查询更新tblAbsence.Status字段

Alright, let's tackle this update query for your leave approval logic. First, let's recap the tables and sample data we're working with to make sure we're on the same page:

Tables & Sample Data #

tblAbsence #

This table tracks leave requests with the following fields and sample entries:

tblManning #

This table tracks staffing requirements per date:

Core Requirement #

We need to update tblAbsence.Status to "Approved" for any leave request where all Required values in tblManning (for dates within the leave's start/end range) do not exceed our threshold (here, we're using 4 as the example threshold). Put another way: if there are no dates in the leave period where staffing requirements go over 4, the request gets approved.

Solution Query #

Based on the logic you mentioned (updating records that aren't in the problematic subquery results), here's the SQL statement you need:

UPDATE tblAbsence
SET Status = 'Approved'
WHERE NOT EXISTS (
    -- Subquery checks if there's any date in the leave period with Required > threshold
    SELECT 1
    FROM tblManning
    WHERE tblManning.Date BETWEEN tblAbsence.Start AND tblAbsence.End
      AND tblManning.Required > 4 -- Replace with your actual threshold if needed
);

Breakdown of the Logic #

• The NOT EXISTS clause is key here: it looks for any date in the leave request's range where Required exceeds the threshold. If no such date exists, the subquery returns no rows, so NOT EXISTS evaluates to true, and we update the status to Approved.
• This also handles leave periods that include dates not present in tblManning—since there's no Required value to exceed the threshold, those requests will also be marked as Approved. If you need to treat missing dates differently (e.g., only approve if all dates in the range exist in tblManning), we can adjust the query, but this matches the core requirement you described.

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