CODEWITHSUNDEEP
sqlsql-server
Zee-pro
Zee-pro

Reputation: 165

Updating whole Table where value in NULL [Without indication each column separately]

I need help with updating whole table to 0[Zero], where value in NULL. Without indication each column separately.

Upvotes: 0

Views: 258

Answers (1)

arserbin3
arserbin3

Reputation: 6148

It can be done, you just first have to extract all the column names from INFORMATION_SCHEMA or elsewhere, build the SQL into a string, then execute it:

USE MyDatabase
DECLARE @TableName NVARCHAR(100)
SELECT @TableName = 'MyTable'

DECLARE @Sql NVARCHAR(4000) 
    SELECT @Sql = COALESCE(@Sql + '; ', '')
        + 'UPDATE dbo.'
        + @TableName
        + ' SET '
        + COLUMN_NAME
        + ' = 0 WHERE '
        + COLUMN_NAME
        + ' IS NULL'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
        AND IS_NULLABLE = 'YES'
        AND DATA_TYPE NOT IN ('text','ntext','image')

SELECT @Sql
EXEC(@Sql)

Be sure to set MyDatabase and MyTable accordingly

Upvotes: 1

Related Questions