CODEWITHSUNDEEP
sqlsql-server-2005sql-update
ImGreg
ImGreg

Reputation: 2993

SQL Update Multiple Rows with Multiple Values

I have a list of items that I need to update based on their unique ID in a SQL Server 2005 environment. I was wondering what the best way of writing a script to update these items.

I know I can simply update a column by writing multiple queries such as:

UPDATE myTable
SET HelpLink = 'newLink'
WHERE ID = 5

UPDATE myTable
SET HelpLink = 'newLink2'
WHERE ID = 6

Is there any other way of doing this without having to repeat the above update about 20 times? Repeating the above tends to make a pretty ugly update script.

NOTE: I have a bulk set of items that I will be updating by their unique ID, these items are not coming from a database table.

Upvotes: 1

Views: 986

Answers (2)

ImGreg
ImGreg

Reputation: 2993

I found out that you can use case statements which seems to simplify things quite a bit. This allows me to add multiple items into a single query. 

UPDATE [MyTable]
SET HelpLink = CASE ID
        WHEN 2 THEN 'MKSDefectsChart.png'
        WHEN 6 THEN 'EPMRisks.png'
        WHEN 7 THEN 'DCTSHardwareChanges.png'
        ELSE NULL
    END
WHERE ID IN (2, 6, 7)

Upvotes: 1

user1455836
user1455836

Reputation: 752

You can always update from another table like 

update myTable
set HelpLink = myOtherTable.HelpLink
from myOtherTable
where myTable.[ID] = myOtherTable.[ID]

You'll have to create that other table though

Upvotes: 0

Related Questions