how can we update null value columns in a table in sql serve

Ankit Agarwal

13y4.6k

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Priyanka Jain13y ago
Now i am explaining this with the help of example.

suppose your table name is tab1
and table have following structures and data

so you have to write

update tab1 set name='n' where name is null

update tab1 set pincode='6' where pincode is null
0
Iftikar Hussain13y ago
by above query you will get list of column name which are null.
0
Ankit Agarwal13y ago
many columns in my table so, what should be name of columns,
will we write every column name?
0
Rosi Sreenivasa Reddy13y ago
hai Hussain,
can you explain clearly about it?

0
Iftikar Hussain13y ago
Hi,
By using the below query you can find all column which are null

DECLARE @sql NVARCHAR(MAX);
DECLARE @table NVARCHAR(30);

SET @table = N'EmployeesBackUp';
SET @sql = STUFF((
SELECT N' UNION ALL UPDATE @table SET ' + column_name + '=1 ' +
@table + N' HAVING MAX(' + column_name + N') IS NULL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @table
FOR XML PATH('')), 1, 11, '');

exec(@sql)

Regards,
Iftikar
0
Priyanka Jain13y ago
solution is:

update tablename set columnname=value where columnname is null
0