CODEWITHSUNDEEP
sqlsqlite
Chicowitz
Chicowitz

Reputation: 5939

SQLite multiple UPDATES in one query

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

Answers (2)

Jibin Balachandran
Jibin Balachandran

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

Tim Biegeleisen
Tim Biegeleisen

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

Related Questions