CODEWITHSUNDEEP
sqlsql-server-2008-r2
sooprise
sooprise

Reputation: 23187

sql update many rows based on value in each row

I have a really inefficient sql script that takes the following table, and populates val2 using a cursor. I want to be able to update all of the val2 values with a single update, but val2 is calculated using val1 for each row. 

val1: 1, val2 0
val1: 2, val2 0 
val1: 3, val2 0
val1: 4, val2 0
val1: 5, val2 0

So I imagine, you'd have something like this:

UPDATE Table SET val2 = (some code based on val1 for each row)

How can I write this into a single line of sql?

Upvotes: 0

Views: 818

Answers (1)

Eric
Eric

Reputation: 95203

update table set val2 = val1 + 1

Works just like that! If you have some set of codes that you want to have applied by a case statement:

update table set
    val2 = 
        case 
            when val1 = 1 then 'One'
            when val1 = 2 then 'Two'
            when val1 = 3 then 'Three'
            else 'Something something'
        end

Upvotes: 5

Related Questions