SQL语句问题：更新同fldAllocatedLeadId的指定状态行异常

Let's break down what's going wrong with your current query and fix it step by step.

What's Wrong With the Original Query? #

• You have an extra AND in your subquery (right after WHERE) which causes a syntax error.
• Your CASE statement doesn't include an ELSE clause. That means any row that doesn't meet your condition will have fldAllocatedStatus set to NULL instead of keeping its original value—this is why your Accepted and Rejected rows are getting messed up.
• The nested subquery (SELECT * FROM tblAllocatedLeads) is unnecessary and inefficient; you can directly count duplicates without copying the entire table.

Corrected SQL Query #

Here's a refined version that targets only the rows you want:

UPDATE tblAllocatedLeads
SET fldAllocatedStatus = 'Outstanding'
WHERE 
  fldAllocatedStatus = 'Allocated'
  AND fldAllocatedLeadId IN (
    SELECT fldAllocatedLeadId
    FROM tblAllocatedLeads
    GROUP BY fldAllocatedLeadId
    HAVING COUNT(*) > 1
  );

How This Works #

1. Subquery: The inner SELECT finds all fldAllocatedLeadId values that appear more than once in the table using GROUP BY and HAVING COUNT(*) > 1.
2. WHERE Clause: We filter to only update rows where:
   • The current status is 'Allocated' (so we leave Accepted/Rejected rows untouched)
   • The lead ID is in the list of duplicates we found
3. Direct Update: Instead of using a CASE statement (which risks NULLs without ELSE), we directly set the status to 'Outstanding' only for matching rows—all other rows remain unchanged.

Alternative: Using Window Functions (For Modern Databases) #

If your database supports window functions (like MySQL 8.0+, PostgreSQL, SQL Server), you can use this cleaner approach:

WITH DuplicateLeads AS (
  SELECT 
    ID,
    fldAllocatedStatus,
    COUNT(*) OVER (PARTITION BY fldAllocatedLeadId) AS lead_count
  FROM tblAllocatedLeads
)
UPDATE tblAllocatedLeads
SET fldAllocatedStatus = 'Outstanding'
FROM DuplicateLeads
WHERE 
  tblAllocatedLeads.ID = DuplicateLeads.ID
  AND DuplicateLeads.fldAllocatedStatus = 'Allocated'
  AND DuplicateLeads.lead_count > 1;

Testing With Your Sample Data #

For your test set:

Both queries will:

• Leave IDs 1, 2, and 4 unchanged
• Update ID 3's status to 'Outstanding'—exactly your expected result.

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