Posted by Plamen Ratchev on 01/31/07 14:50

Yes, the query will update an existing table, just keep adding lines for
each column, like this:

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end),
nameval3 = (case when nametype1 = nametype5 then 2 else 1 end),
...

As for you reference to do the update in a loop, then this is doable via
dynamic SQL. Here is a quick sketch of how it may look (by adjusting the
number 10 you will get different number of columns, beware of the 4000
character limit on the SQL string):

DECLARE @sql nvarchar(4000),
@count int,
@numcolumns int

SELECT @sql = 'update test set ', @count = 1, @numcolumns = 10

WHILE @count <= @numcolumns
BEGIN
IF @count = 1
SELECT @sql = @sql + 'nameval = (case when nametype1 = nametype' +
CAST(@count + 1 as nvarchar) + ' then 2 else 1 end)'
ELSE
SELECT @sql = @sql + ', nameval' + CAST(@count - 1 as nvarchar) + ' =
(case when nametype1 = nametype' + CAST(@count + 1 as nvarchar) + ' then 2
else 1 end)'

SELECT @count = @count + 1
END

EXEC(@sql)

Erland Sommarskog has an excellent guide on dynamic SQL at
http://www.sommarskog.se/dynamic_sql.html. I would recommend reading it
before jumping into using dynamic SQL. Also, please read the comment from
Celko, he is correct that without posting DLL and specifications it is very
difficult to get good answers.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]