Doubt in Query - SQL Server 2005

I am having table with 100 columns. here up to 50 to 60 columns contains NU开发者_JAVA百科LL value in it. Now i need to Replace this NULL value to 0 in all 50 to 60 columns. I tried with the Update query as,

UPDATE [tableName] 
SET col1=0, col2 = 0, ... col60 = 0 
WHERE col1 IS NULL AND Col2 IS NULL ... Col60 IS NULL

Is there anyother Query to update these all 60 columns without specifying such columns or we have any other approach???

You have to specify all columns, but you can skip the WHERE clause and have one update deal with them all at once:

UPDATE [tableName] SET
  col1=COALESCE(col1, 0),
  col2=COALESCE(col2, 0),
  col3=COALESCE(col3, 0),
  col4=COALESCE(col4, 0),
  [...]

You could try this workaround if every value in the columns is NULL:

• Edit the table definition and set the columns as "Calculated" and use 0 as formula
• Save the table
• Remove the formula

It is not very elegant but works

I don't think there's an alternative - but the query you posted will only update records where all the columns are null.

If you want to update individual columns, you need to break it up into individual updates:

update table
set col1 = 0
where col 1 is null

update table
set col2 = 0
where col2 is null

To do not write this query by hand, you can generate this by using dynamic SQL:

DECLARE @Table NVARCHAR(255)= 'Your table'
DECLARE @sSQl NVARCHAR(MAX)= 'UPDATE ' + @Table + ' SET ' + CHAR(13) ;
WITH    c AS ( SELECT   c.name
               FROM     sys.all_columns c
                        JOIN sys.tables T ON c.object_id = T.object_id
               WHERE    t.name = @Table
             )
    SELECT  @sSQl = @sSQl + c.name + '=ISNULL(' + c.name + ',0)' + ','
            + CHAR(13)
    FROM    c 
IF LEN(@sSQl) > 0 
    SET @ssql = LEFT(@sSQl, LEN(@sSQl) - 2) 

PRINT @ssql