CODEWITHSUNDEEP
sqlsql-update
Will
Will

Reputation: 8631

Updating multiple values within the one column

I have the following SQL which I have written to update a tables values:

Update Table
SET S_Type = 'Versus'
Where S_Type = 'REGULAR'
SET S_Type = 'Free'
Where S_Type = 'CASH';

My SQL is rather rusty, and my colleague told me something was up with it but didn't tell me what!

The only thing that comes to mind is I have not referred to the Table.Column in the set and Where code.

Is there any issue updating a column as such? What is the best practice when updating a column for multiple values?

Cheers

Upvotes: 2

Views: 22593

Answers (3)

Vikram Jain
Vikram Jain

Reputation: 5588

Here , we are using case statement and find result like as where clause :

update tablename
set S_Type = (case S_Type  when 'REGULAR' then 'Versus'
                           when 'CASH' then 'free' 
                           else s_type 
                           end)

Upvotes: 6

Myles J
Myles J

Reputation: 2880

Are you using SQL Server? If you are using 2012 you can now write simplified conditional update statements using the new CHOOSE or IIF features e.g:

Update YourTable
set S_Type = IIF(S_Type = 'REGULAR', 'Versus', 'free')

My two pennies worth.

Upvotes: 0

podiluska
podiluska

Reputation: 51494

Update YourTable
set S_Type = 
    case S_Type 
        when 'REGULAR' then 'Versus'
        when 'CASH' then 'free' 
        else s_type 
        end

Upvotes: 2

Related Questions