Click here to Skip to main content
15,879,096 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello,

how can we update null value columns in whole table in sql server, without using column name?

I want to update all null value columns in a table, many columns in my table, So, can we update all null value columns without using column name?

Please help me.

Thanks in advance.

Ankit Agarwal
Software Engineer
Posted

1 solution

For That u have to Use Dynamic Queries...
Try Some thing Like this...
In this below example am updating only columns of datatype nvarchar... u can do like this..
SQL
Create Table Temp (Item Nvarchar(100), Col1 Nvarchar(10),Col2 Nvarchar(40))
Insert into Temp 
Select 'Item1',Null,'Test1' Union All
Select 'Item1',Null,Null Union All
Select 'Item1',Null,'Test1'Union All
Select 'Item1',Null,'Test1' Union All
Select 'Item1','Test1',Null

Declare @ColumnName Varchar(100),@Pos Int,@PrevPos Int=0,@UpdatedValue varchar(100),
        @Query varchar(1000),@TableName Varchar(40)

Select  @TableName= 'Temp',@UpdatedValue='''Test'''

While 1>0
Begin
    Set @ColumnName=''
    Select Top(1) @ColumnName = COLUMN_NAME,@Pos= ORDINAL_POSITION 
    From INFORMATION_SCHEMA.COLUMNS  
    Where TABLE_NAME=@TableName and IS_NULLABLE ='YES' and DATA_TYPE ='nvarchar'
    and ORDINAL_POSITION > @PrevPos

    If @ColumnName =''
       Break;
	
    Select @Query= 'Update ' + @TableName + ' Set '  + @ColumnName + ' = ' + 
    @UpdatedValue  +' Where ' + @ColumnName +' is null' , @PrevPos=@Pos 

    Exec(@Query)	
End

Select * from Temp 
Drop Table Temp

Output:
SQL
Item	Col1	Col2
-----  -----  -------
Item1	Test	Test1
Item1	Test	Test
Item1	Test	Test1
Item1	Test	Test1
Item1	Test1	Test
 
Share this answer
 
v3
Comments
Zoltán Zörgő 29-Aug-13 8:48am    
Great! +5!
Raja Sekhar S 29-Aug-13 8:50am    
Thank you Zoltan Zorgo....

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900