Update multiple field values in place 1

[wbodger]

OK, I could not come up with a better subject, but here is what I need to do in transforming my data. I have been given data with one field having the values 1,2,3,4,5,6,9. All values that I expect, but they mean something different than I want, so I need to change things up a bit. Where they put 3 it should be 4, where they put 4 it should be 5, where they put 5 it should be 6 and where they put 6 it should be 3. Now, I know I can update this easily enough, but my question is this.

If I write

update table
set field = 
case
when 3 then 4
when 4 then 5
when 5 then 6
when 6 then 3
end

what is the order going to be? Will it update all of the 3s to 4s and then take all of the fours (old and new) and make them 5s and so on, or will it just update from the original value?

Thanks!
wb

 

[gmmastros]

Everything will be done in "one shot" and will happen exactly the way you want it to. Notice that in the code I show below, I added an ELSE to the case when expression.

Declare @Temp Table(Field Int, Value VarChar(10))

Insert Into @Temp Values(1, 'one')
Insert Into @Temp Values(2, 'two')
Insert Into @Temp Values(3, 'three')
Insert Into @Temp Values(4, 'four')
Insert Into @Temp Values(5, 'five')
Insert Into @Temp Values(6, 'six')
Insert Into @Temp Values(7, 'seven')
Insert Into @Temp Values(8, 'eight')
Insert Into @Temp Values(9, 'nine')

Select 'Before', * From @Temp

update @Temp
set field = 
case field
when 3 then 4
when 4 then 5
when 5 then 6
when 6 then 3
else field
end 


Select 'After', * From @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

I just noticed in a test that without that else 1 and 2 are set to null. Thanks!

 

[gmmastros]

Interestingly enough, this works for swapping column values.

In procedural languages, if you want to swap the value of 2 variables, you have to introduce a 3rd variable, something like this:

VarA = 1
VarB = 7

VarTemp = VarA
VarA = VarB
VarB = VarTemp

This is not necessary in SQL.

Declare @Temp Table(Field1 Int, Field2 Int)

Insert Into @Temp Values(1, 100)
Insert Into @Temp Values(2, 200)
Insert Into @Temp Values(3, 300)
Insert Into @Temp Values(4, 400)
Insert Into @Temp Values(5, 500)
Insert Into @Temp Values(6, 600)

Select 'Before', * From @Temp

update @Temp
set    Field1 = Field2,
       Field2 = Field1

Select 'After', * From @Temp

This works because of the set nature of the SQL language.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

That is pretty cool. I really appreciate the insight/experience that you and others share here. It is very helpful.

wb