SQL Server中UPDATE语句失效，求修正：按条件更新i_status字段

Alright, let's fix that UPDATE statement for you. Let's start by breaking down what you're trying to accomplish, then walk through the correct syntax for SQL Server.

Core Requirements Recap #

You need to update records in test_emp where desig is 'it', 'hr', or 'account':

• Set i_status to 'Y' only if both i_flag and i_deleted are 'Y'
• Otherwise set i_status to 'N'
• You mentioned grouping by eid, pid, and the CASE result — I'll cover both row-level and group-level update scenarios below, since grouping directly in an UPDATE isn't allowed in SQL Server.

Scenario 1: Update Each Row Individually (Most Common Use Case) #

If you just need to evaluate each row's own i_flag and i_deleted values to set i_status, this simple, direct UPDATE will work:

UPDATE test_emp
SET i_status = 
    CASE 
        WHEN i_flag = 'Y' AND i_deleted = 'Y' THEN 'Y'
        ELSE 'N'
    END
WHERE desig IN ('it', 'hr', 'account');

How this works:

• The WHERE clause filters down to only the records you care about (matching desig values).
• The CASE expression checks each row's specific flags and sets i_status accordingly.

Scenario 2: Update Based on Group-Level Logic #

If your grouping requirement means you want all rows in the same eid/pid group to share the same i_status (e.g., if any row in the group meets the i_flag='Y' + i_deleted='Y' condition, set all rows in the group to 'Y'), you'll need to use a CTE to calculate group-level status first, then join back to update the table:

WITH EmpGroupStatus AS (
    SELECT
        eid,
        pid,
        -- Calculate the status for the entire group
        CASE
            WHEN EXISTS (
                SELECT 1
                FROM test_emp sub
                WHERE sub.eid = main.eid
                  AND sub.pid = main.pid
                  AND sub.i_flag = 'Y'
                  AND sub.i_deleted = 'Y'
                  AND sub.desig IN ('it', 'hr', 'account')
            ) THEN 'Y'
            ELSE 'N'
        END AS group_i_status
    FROM test_emp main
    WHERE main.desig IN ('it', 'hr', 'account')
    GROUP BY eid, pid
)
UPDATE te
SET te.i_status = eg.group_i_status
FROM test_emp te
JOIN EmpGroupStatus eg 
    ON te.eid = eg.eid 
    AND te.pid = eg.pid
WHERE te.desig IN ('it', 'hr', 'account');

Why your original statement likely failed:

SQL Server doesn't support using GROUP BY directly in an UPDATE clause. You first need to compute aggregated/grouped values in a subquery or CTE, then join that result back to the original table to apply the updates.

If you had a different grouping intent (e.g., updating only one row per group), let me know and we can adjust the query further!

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