Reputation: 5939
In table Colors, there is a Color column. I need to make 5 updates to the values in this column based on different conditions.
SET Color = 'Blue' WHERE Color = 'blue'
SET Color = 'Green' WHERE Color = 'green'
SET Color = 'Yellow' WHERE Color = 'yellow'
SET Color = 'Brown' WHERE Color = 'brown'
SET Color = 'Orange' WHERE Color = 'orange'
Can I make these 5 updates to the table in one query?
Note: my real values are more complex than this, so disregard simple functions to make the values uppercase
Upvotes: 3
Views: 5253
Reputation: 3441
You can make use of CASE
expression and decide which value you want to set to Color
.
UPDATE [YourSchema].[Colors]
SET Color= CASE
WHEN Color = 'blue' THEN 'Blue'
WHEN Color = 'green' THEN 'Green'
WHEN Color = 'yellow' THEN 'Yellow'
WHEN Color = 'brown' THEN 'Brown'
WHEN Color = 'orange' THEN 'Orange'
END
WHERE Color IN ('blue', 'green', 'yellow', 'brown', 'orange')
Note: If you are having default schema then use dbo.Colors
as the table name,
Upvotes: 5
Reputation: 522817
As an alternative to a lengthy CASE
expression, you can simply do a conditional uppercasing of the first letter of a color should that color match the ones you gave in your list.
update Colors
set Color = upper(substr(Color, 1, 1)) || substr(Color, 2)
where Color in ('blue', 'green', 'yellow', 'brown', 'orange')
Upvotes: 3