如何编写SQL语句实现一次性批量更新多条APS_Dev数据？

Got it, let's tackle this batch update scenario for you. Instead of running separate UPDATE statements (which works, but isn't the most efficient or maintainable), we can leverage a JOIN with a mapping dataset to handle all your value pairs in a single query. Here's how to do it properly:

Option 1: Using a Table Variable (Great for Reusable Mappings) #

If you might need to reuse the mapping logic later, or have a lot of pairs to add, a table variable keeps things organized:

-- Declare a table to hold our APS_Dev <-> Style_Color/Cluster_ID mappings
DECLARE @UpdateMappings TABLE (
    Target_APS_Dev INT,
    Match_Style_Color VARCHAR(10),
    Match_Cluster_ID INT
);

-- Populate the table with your specific value pairs
INSERT INTO @UpdateMappings (Target_APS_Dev, Match_Style_Color, Match_Cluster_ID)
VALUES 
    (1, '0012', 3456),  -- APS_Dev = 1 when Style_Color=0012 and Cluster_ID=3456
    (2, '0013', 4567);  -- APS_Dev = 2 when Style_Color=0013 and Cluster_ID=4567

-- Perform the batch update by joining your target table to the mappings
UPDATE target_table
SET target_table.APS_Dev = um.Target_APS_Dev
FROM <your_table_name> AS target_table
INNER JOIN @UpdateMappings AS um
    ON target_table.Style_Color = um.Match_Style_Color
    AND target_table.Cluster_ID = um.Match_Cluster_ID;

Option 2: Using a Derived Table (Simpler for One-Time Updates) #

If this is a one-off update and you don't need to reuse the mappings, you can skip the table variable and use a derived VALUES list directly:

UPDATE target_table
SET target_table.APS_Dev = mt.Target_APS_Dev
FROM <your_table_name> AS target_table
INNER JOIN (
    -- Define your mappings directly here
    VALUES 
        (1, '0012', 3456),
        (2, '0013', 4567)
) AS mt (Target_APS_Dev, Match_Style_Color, Match_Cluster_ID)
    ON target_table.Style_Color = mt.Match_Style_Color
    AND target_table.Cluster_ID = mt.Match_Cluster_ID;

Key Notes to Remember: #

• Quotes for String Values: Notice I added single quotes around '0012' and '0013'—since Style_Color looks like a string (it has leading zeros), you need to treat it as a string to avoid implicit conversion errors.
• Data Type Matching: Make sure the data types in your mapping table/derived list match exactly with the columns in your target table (e.g., Cluster_ID should be the same INT type as in your table).
• Safety First: Always run a SELECT first to verify which rows will be updated, before executing the UPDATE. For example:

  SELECT target_table.*, um.Target_APS_Dev
  FROM <your_table_name> AS target_table
  INNER JOIN @UpdateMappings AS um
      ON target_table.Style_Color = um.Match_Style_Color
      AND target_table.Cluster_ID = um.Match_Cluster_ID;
  

This approach is far better than multiple UPDATE statements because it minimizes round-trips to the database, is easier to maintain (just add new rows to the mappings), and reduces the risk of missing a pair or making typos across multiple queries.

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