Update null to unknown in a table for all the columns

Question

Post reply

Update null to unknown in a table for all the columns

Kumara1011

SSC-Addicted

Points: 424
More actions
November 26, 2012 at 4:19 am

#285433

Hi,
i need query to update all the columns which ever carrying null to 'unknown' using a single query or procedure or a function.
Can any one suggest?
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

November 26, 2012 at 4:24 am

#1562135

You'll be looking for the COALESCE function.

John

Ant-Green SSC Guru Points: 113498 More actions · Answer 2

Reddy Ksr (11/26/2012)
Hi,
i need query to update all the columns which ever carrying null to 'unknown' using a single query or procedure or a function.
Can any one suggest?
Thanks in advance.

Just one point, how do you plan to store 'unknown' in a column which doesnt accept strings?

WolfgangE SSCertifiable Points: 6328 More actions · Answer 3

Why do you want to set the value "unknown" at all? The NULL already tells you that the content ist unknown. The way mentioned by John is for using in select statements and should be enough. Otherwise you should ask yourself which benefit you get of this update.

If you really want to store the string value "unknown" in the table column you have to choose an appropriate data type.

Greetings, Wolf

Kumara1011 SSC-Addicted Points: 424 More actions · Answer 4

Datatype for all the columns is nvarchar only.

we had such a requirement.

WolfgangE SSCertifiable Points: 6328 More actions · Answer 5

There is no simple query for doing this update for all colums of all tables. If you only have to do it once you could use dynamic SQL.

You get the names of your tables and columns out of the system tables sys.tables and sys.columns. Have a look at books online for more details.

You can use the names to generate strings that represent your update statements and execute them. Something like this:

select

'update ' +

schema_name( t.schema_id ) + '.' + t.name +

' set ' + c.name + ' = ''unknown'' where ' + c.name + ' is null'

from sys.tables t inner join

sys.columns c on

c.[object_id] = t.[object_id]

where t.name = 'yourTableName'

order by t.name, c.name

Copy and paste the result and execute it.

If you need it fully automated you have to save each line of the result in a variable and execute it using the exec-statement.

This is not recommended if you need it several times as there are disadvanteges using dynamic sql and the performance of this procedure will not be very well.

Greetings, Wolf

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

This sounds like a poorly thought out requirement. How will you know if the column should be NULL because the value is unknown or if the actual value is 'unknown'. This to me sounds like a knee jerk reaction from somebody who does not understand how to work with NULLS.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

weberharter (11/26/2012)
There is no simple query for doing this update for all colums of all tables. If you only have to do it once you could use dynamic SQL.
You get the names of your tables and columns out of the system tables sys.tables and sys.columns. Have a look at books online for more details.
You can use the names to generate strings that represent your update statements and execute them. Something like this:
Copy and paste the result and execute it.
If you need it fully automated you have to save each line of the result in a variable and execute it using the exec-statement.
This is not recommended if you need it several times as there are disadvanteges using dynamic sql and the performance of this procedure will not be very well.
Greetings, Wolf

You do realize that your suggestion is very dangerous and might eventually fail?

The OP only needed to update one table (and I hope it's a very special table).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ajay.Kedar Old Hand Points: 329 More actions · Answer 8

Is this requirement to show null column value to 'unknown' then I will suggest to check null value whenever you write select query on null columns and use IsNull(<col_Name>,'unknown')

If your requirement is just to update at database column values then it does'nt work. But I would say updating database column values you will loose null values from database forever.

Ajay.Kedar Old Hand Points: 329 More actions · Answer 9

Is this requirement to show null column value to 'unknown' then I will suggest to check null value whenever you write select query on null columns and use IsNull(<col_Name>,'unknown')

If your requirement is just to update at database column values then it does'nt work. But I would say updating database column values you will loose null values from database forever.

Regards,

Ajay Kedar