Help Simply the Update Statement

Hello all,

I need some help in simplyfying the following update statement -

update table <table_a> set <col_1> = NULL where <col_1> = 'N/A'
update table <table_a> set <col_2> = NULL where <col_2> = 'N/A'
update table <table_a> set <col_3> = NULL where <col_3> = 'N/A'
update table <table_a> set <col_4> = NULL where <col_4> = 'N/A'
update table <table_a> set <col_5> = NULL where <col_5> = 'N/A'
update table <table_a> set <col_6> = NULL where <col_6> = 'N/A'
update table <table_a> set <col_7> = NULL where <col_7> = 'N/A'
...and there are 73 columns

Anyway I can create a loop or array and store the column name as a parameter and then pass it to the update statement?

Thanks in advance,
SauravPut this in a SP and keep @.table as parameter

DECLARE @.COLUMN VARCHAR(100)
DECLARE @.TABLE VARCHAR(100)

CREATE TABLE #TEMP_TABLE
(
COLUMNNAME VARCHAR(100)
)

SET @.TABLE = 'PRODUCTS'

INSERT INTO #TEMP_TABLE

SELECT A.NAME FROM
SYSCOLUMNS AS A
INNER JOIN
SYSOBJECTS AS B
ON
A.ID = B.ID
WHERE
B.NAME = @.TABLE --TABLE NAME

WHILE ((SELECT COUNT(*) FROM #TEMP_TABLE) > 0)
BEGIN

SELECT TOP 1 @.COLUMN = COLUMNNAME
FROM #TEMP_TABLE

EXEC('UPDATE' + @.TABLENAME + ' SET ' + @.COLUMN + ' = NULL WHERE ' + @.COLUMN + ' = ''N/A''')

DELETE FROM #TEMP_TABLE WHERE COLUMNNAME = @.COLUMN

END

DROP #TEMP_TABLE|||There's rarely need to send table names to sprocs for dynamic sql, and this isn't one of those times in any case. It's all on the same table.

Simpler:
update table <table_a>
set <col_1> = NULLIF(<col_1>, 'N/A')
, <col_2> = NULLIF(<col_2>, 'N/A')
, <col_3> = NULLIF(<col_3>, 'N/A')
....
FROM <table_a>
WHERE <col_1> = 'N/A'
OR <col_2> = 'N/A'
OR <col_3> = 'N/A'...|||You rock, Blindman. Thanks you!!|||You rock, Blindman. Thanks you!!I'll be sure to tell him ;)

--blindman is quoted in my sig - he ain't me and he will be pretty appalled when he sees this.|||Thank you nick.cs! I also found that there is no need to update all columns but around 28 of them. But new columns may be added to the update statement and removed. Therefore, I have added another variable to your query which is a flag field to indicate which ones are needed to be updated. Your query also helped me!

Thanks all!

This forum is great!