0

Hi all,
I am trying to update Table[land] with the following data (simplified):

Scenarios:

Num_1 like [A-Z], Num_1_Index like [1-9]
Num_2 like [A-Z], Num_2_Index like [1-9]
.....
Num_26 = [A-Z], Num_26_Index = [1-9]
---------------------------------------------
Update land
set Num_1_Index = case
when Num_1 like 'A' then '1'
when Num_1 like 'B' then '2'
......
when Num_1 like 'Z' then '26'
else Num_1_Index
end
go

Update land
set Num_2_Index = case
when Num_2 like 'A' then '1'
when Num_2 like 'B' then '2'
......
when Num_2 like 'Z' then '26'
else Num_2_Index
end
go
.....
.....

Update land
set Num_26_Index = case
when Num_26 like 'A' then '1'
when Num_26 like 'B' then '2'
......
when Num_26 like 'Z' then '26'
else Num_26_Index
end
go

Objectives
Anything after "then" is fixed standardization. Anything after like is general data.
I want to update all the columns w/ the same standardizations. w/out replicating the query 26 times.

Thank you very much for the time.

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by sknake
0

Create a user defined function to do the value mapping and:

Update Table Set Column1 = dbo.GetValue('abc'), Column2=dbo.GetValue('def')

Post your complete query here if that doesn't make sense.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.