SQL Server 2014批量更新指定条件两列的高效SQL查询求助

Instead of running separate UPDATE statements for each tag, you can handle all these changes in a single query. Here are two clean approaches depending on your needs:

Approach 1: Using CASE Statements (Best for Small Number of Mappings) #

This is straightforward if you only have a few tag pairs to update. It keeps everything in one UPDATE block without needing extra tables:

UPDATE YourTableName
SET 
    tag_name = CASE tag_name
        WHEN 'xxxxx' THEN 'xxxx2'
        WHEN 'yyyyy' THEN 'yyyy2'
        WHEN 'zzzzz' THEN 'zzzz2'
        ELSE tag_name -- Leave other values unchanged
    END,
    Hierarchical_Name = CASE tag_name
        WHEN 'xxxxx' THEN 'xxxx2'
        WHEN 'yyyyy' THEN 'yyyy2'
        WHEN 'zzzzz' THEN 'zzzz2'
        ELSE Hierarchical_Name -- Leave other values unchanged
    END
WHERE tag_name IN ('xxxxx', 'yyyyy', 'zzzzz'); -- Only update rows that match our targets

Key Notes:

• Replace YourTableName with the actual name of your table.
• The ELSE clause ensures rows not in your target list stay untouched.
• The WHERE clause limits the update to only the rows that need changing, which is far more efficient than updating every row in the table.

Approach 2: Using a Join with a Value List (Better for Scalable Mappings) #

If you might add more tag pairs later, this approach is easier to maintain. We'll create an inline table of old/new values and join it to your table:

UPDATE t
SET 
    t.tag_name = nv.new_value,
    t.Hierarchical_Name = nv.new_value
FROM YourTableName t
JOIN (
    VALUES 
        ('xxxxx', 'xxxx2'),
        ('yyyyy', 'yyyy2'),
        ('zzzzz', 'zzzz2')
) AS nv(old_value, new_value)
ON t.tag_name = nv.old_value;

Why this works:

• The inline VALUES clause acts as a temporary lookup table for your tag mappings.
• The join ensures only rows with matching tag_name values get updated.
• Adding new mappings is as simple as adding another row to the VALUES list.

Bonus: Verify Changes Before Updating #

Always a good idea to check which rows will be updated before running the actual UPDATE. Use a SELECT instead:

-- For Approach 1
SELECT 
    tag_name AS original_tag,
    CASE tag_name
        WHEN 'xxxxx' THEN 'xxxx2'
        WHEN 'yyyyy' THEN 'yyyy2'
        WHEN 'zzzzz' THEN 'zzzz2'
        ELSE tag_name
    END AS new_tag,
    Hierarchical_Name AS original_hierarchy,
    CASE tag_name
        WHEN 'xxxxx' THEN 'xxxx2'
        WHEN 'yyyyy' THEN 'yyyy2'
        WHEN 'zzzzz' THEN 'zzzz2'
        ELSE Hierarchical_Name
    END AS new_hierarchy
FROM YourTableName
WHERE tag_name IN ('xxxxx', 'yyyyy', 'zzzzz');

-- For Approach 2
SELECT 
    t.tag_name AS original_tag,
    nv.new_value AS new_tag,
    t.Hierarchical_Name AS original_hierarchy,
    nv.new_value AS new_hierarchy
FROM YourTableName t
JOIN (
    VALUES 
        ('xxxxx', 'xxxx2'),
        ('yyyyy', 'yyyy2'),
        ('zzzzz', 'zzzz2')
) AS nv(old_value, new_value)
ON t.tag_name = nv.old_value;

Both methods work perfectly in SQL Server 2014 and will handle your 10k-row table efficiently.

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