CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
whytheq
whytheq

Reputation: 35557

In this example can UPDATE statements be combined?

I quite often have rows of code like the following:

    UPDATE my_table SET name = 'x'  WHERE Original =  'a'
    UPDATE my_table SET name = 'y' WHERE Original =  'b' 
    UPDATE my_table SET name = 'z' WHERE Original = 'c'  
    UPDATE my_table SET name = 'k' WHERE Original = 'd' 
    UPDATE my_table SET name = 'm' WHERE Original = 'e'  
    UPDATE my_table SET name = 'n' WHERE Original = 'f'

Can I combine/shorten this code into one UpDate statement - or are they best just left as they are?

Upvotes: 0

Views: 85

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

You could use a table value constructor and a from clause, if these values aren't already in a table:

update mt set name = t.name
from
    my_table mt
        inner join
    (values
        ('a','x'),
        ('b','y'),
        ('c','z'),
        ('d','k'),
        ('e','m'),
        ('f','n')
    ) t(original,name)
        on
            mt.Original = t.original

Upvotes: 1

Andomar
Andomar

Reputation: 238086

You could use a case statement:

UPDATE  my_table 
SET     name = 
        case Original
        when 'a' then 'x'
        when 'b' then 'y'
        ...
        else name -- Preserve original
        end

The else clause makes sure you're not modifying a name if it's not matched in the case.

Upvotes: 4

Sean
Sean

Reputation: 15144

UPDATE my_table 
SET name = 
    CASE 
        WHEN Original = 'a' THEN 'x' 
        WHEN Original = 'b' THEN 'y' 
        ... 
    END

That will update EVERY row. So if there's an Original value you haven't specified, it will be set to NULL. So you might want to limit the update to just those you want to update, with a WHERE clause, like so:

WHERE Original IN ('a', 'b', ...)

OR, as an alternative, you could use an ELSE statement, which leaves the name value as is, if it doesn't have a match in the WHEN statements, like so:

    CASE 
        WHEN Original = 'a' THEN 'x' 
        WHEN Original = 'b' THEN 'y' 
        ... 
        ELSE name
    END

Upvotes: 4

Related Questions