Dynamic update statement with variable column names

You can pass the name of the column in dynamic sql:

declare @sql nvarchar (1000);
set @sql = N'update table set ' + @column_name + '= ''''';

exec sp_executesql @sql;

You can look in the sys.columns table and join on the table name or object_id.

 DECLARE @OBJ_ID INT

 SELECT @OBJ_ID = OBJECT_ID
 FROM SYS.tables
 WHERE name = 'YOURTABLE'

 SELECT * FROM SYS.columns
 WHERE OBJECT_ID = @OBJ_ID

You could use the name field from the sys.columns query as a basis to perform the update on.

Assuming you want all columns of varchar/char types only (or change the type filter to whatever you need):

DECLARE @tableName varchar(10)
SET @tableName = 'yourtablenamehere'

DECLARE @sql VARCHAR(MAX)
SET @sql = ''

SELECT @sql = @sql + 'UPDATE ' + @tableName + ' SET ' + c.name + ' = '''' WHERE ' + c.name + ' IS NULL ;'
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE t.name = @tableName AND y.name IN ('varchar', 'nvarchar', 'char', 'nchar')

EXEC (@sql)

This can be achieved with cursors. You first select the column names like @Darren mentioned, then you Set a Cursor with those values and loop:

Open oColumnsCursor
Fetch Next From oColumnscursor
    Into @ColumnName

While @@FETCH_STATUS=0
    Begin
        Set @oQuery = 'Update [DB]..[Table] Set [' + @ColumnName + '] = ''NewValue'' Where [' + @ColumnName + '] = ''OldValue'''
        Execute(@oQuery)
        Fetch Next From oColumnscursor Into @ColumnName
        Set @oCount = @oCount + 1
    End

Close oColumnsCursor;
Deallocate oColumnsCursor;

This will work when you know the Table Name:

DECLARE @tableName varchar(10)
SET @tableName = 'Customers'

DECLARE @sql VARCHAR(MAX)
SET @sql = ''

SELECT @sql = @sql + 'UPDATE ' + @tableName + ' SET ' + c.name + ' = ISNULL('+ c.name +','''');'
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE y.name IN ('varchar', 'nvarchar', 'char', 'nchar')
AND t.name = @tableName;

EXEC(@sql);

And this will iterate all Tables and all Columns in a Db:

DECLARE @sql VARCHAR(MAX)
SET @sql = ''

SELECT @sql = @sql + 'UPDATE ' + t.name + ' SET ' + c.name + ' = ISNULL('+ c.name +','''');'
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE y.name IN ('varchar', 'nvarchar', 'char', 'nchar');

EXEC(@sql);

Dynamic update statement with variable column names

Related

Recent Posts