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.

no replies?

please help~~~

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.