Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Update null to unknown in a table for all the columns Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 4:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 4:10 AM
Points: 46, Visits: 115
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.
Post #1388515
Posted Monday, November 26, 2012 4:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:51 AM
Points: 5,369, Visits: 9,897
You'll be looking for the COALESCE function.

John
Post #1388518
Posted Monday, November 26, 2012 4:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:20 AM
Points: 5,231, Visits: 5,106
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?





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1388520
Posted Monday, November 26, 2012 4:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:21 AM
Points: 197, Visits: 730
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
Post #1388525
Posted Monday, November 26, 2012 4:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 4:10 AM
Points: 46, Visits: 115
Datatype for all the columns is nvarchar only.

we had such a requirement.
Post #1388529
Posted Monday, November 26, 2012 5:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 29, 2014 5:21 AM
Points: 197, Visits: 730
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
Post #1388541
Posted Monday, November 26, 2012 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:56 AM
Points: 13,443, Visits: 12,306
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1388675
Posted Monday, November 26, 2012 6:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 1,945, Visits: 3,002
Datatype for all the columns is NVARCHAR only.


Weird. There are usually some numeric columns for computations and some temporal columns for time. This has a "bad code smell" to it.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1388907
Posted Monday, November 26, 2012 8:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 3,637, Visits: 7,936
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1388912
Posted Monday, November 26, 2012 11:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:17 AM
Points: 205, Visits: 58
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.
Post #1388957
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse