SQL中CASE WHEN与WHERE的联用方法及UPDATE语句语法错误（'WHERE附近语法不正确'）解决求助

Hey there! Let's tackle your SQL questions step by step.

Combining CASE WHEN and WHERE is totally common in SQL—they serve different purposes and work great together:

• The WHERE clause filters which rows your query will operate on (whether selecting, updating, or deleting).
• The CASE WHEN expression calculates conditional values for those filtered rows.

Here are a couple of practical examples:

Example 1: In a SELECT query #

SELECT
    OrderID,
    ProductName,
    Quantity,
    -- Use CASE to categorize orders based on quantity
    CASE
        WHEN Quantity > 50 THEN 'Bulk Order'
        WHEN Quantity BETWEEN 10 AND 50 THEN 'Standard Order'
        ELSE 'Small Order'
    END AS OrderType
FROM Orders
-- Use WHERE to only include orders from this year
WHERE OrderDate >= '2024-01-01'

Example 2: In an UPDATE query #

UPDATE Products
-- Use CASE to set a new status based on stock level
SET StockStatus = CASE
                    WHEN StockQuantity <= 10 THEN 'Low Stock'
                    WHEN StockQuantity > 100 THEN 'Overstocked'
                    ELSE 'In Stock'
                  END
-- Use WHERE to only update products in the 'Electronics' category
WHERE Category = 'Electronics'

The key takeaway: WHERE narrows down the rows to work with, and CASE WHEN handles the conditional logic for values in those rows.

Looking at your original query, the issue is that you placed the WHERE clause inside the CASE WHEN block, which breaks SQL syntax rules. The CASE WHEN expression needs to be wrapped in CASE ... END, and the WHERE clause for the UPDATE should come after the SET clause to define which rows get updated.

Your Original (Erroneous) Query: #

UPDATE UpdateChecker Set UPDATED = Case WHEN Datediff(DAY, CURRENT_TIMESTAMP, (SELECT TOP(1) ['Debt Market Data$'].Date From ['Debt Market Data$'] ORDER BY Date Desc)) = 0 THEN 'YES' ELSE 'NO' WHERE [TABLE NAME]='Debt Market Data$' END

Corrected Query: #

UPDATE UpdateChecker
SET UPDATED = CASE
                WHEN DATEDIFF(DAY, CURRENT_TIMESTAMP, (SELECT TOP(1) [Date] FROM ['Debt Market Data$'] ORDER BY [Date] DESC)) = 0
                THEN 'YES'
                ELSE 'NO'
              END
-- Move the WHERE clause here, outside the CASE expression
WHERE [TABLE NAME] = 'Debt Market Data$'

What Changed: #

1. Fixed the CASE structure: The CASE now properly closes with END before the WHERE clause.
2. Moved the WHERE clause: It's now in the correct position for an UPDATE statement—after the SET clause—to specify which rows in UpdateChecker should be updated.
3. Simplified the subquery: Removed the redundant table reference ['Debt Market Data$'].Date since we're already querying that table in the subquery.

Optional: Handle NULL Dates #

If the ['Debt Market Data$'] table might be empty (meaning the subquery returns NULL), you can add a fallback to handle that scenario:

UPDATE UpdateChecker
SET UPDATED = CASE
                WHEN (SELECT TOP(1) [Date] FROM ['Debt Market Data$'] ORDER BY [Date] DESC) IS NULL THEN 'UNKNOWN'
                WHEN DATEDIFF(DAY, CURRENT_TIMESTAMP, (SELECT TOP(1) [Date] FROM ['Debt Market Data$'] ORDER BY [Date] DESC)) = 0
                THEN 'YES'
                ELSE 'NO'
              END
WHERE [TABLE NAME] = 'Debt Market Data$'

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