Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Update null to unknown in a table for all the...
Add to briefcase
11 posts, Page 1 of 2
12»»

Update null to unknown in a table for all the columns Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Kumara1011
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, April 30, 2013 5:53 AM
Points: 46, Visits: 108
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
John Mitchell-245523
Posted Monday, November 26, 2012 4:24 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 4,431, Visits: 7,211
You'll be looking for the COALESCE function.

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831
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
WolfgangE
Posted Monday, November 26, 2012 4:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 74, Visits: 420
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
Kumara1011
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, April 30, 2013 5:53 AM
Points: 46, Visits: 108
Datatype for all the columns is nvarchar only.

we had such a requirement.
Post #1388529
WolfgangE
Posted Monday, November 26, 2012 5:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 1:01 AM
Points: 74, Visits: 420
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
Sean Lange
Posted Monday, November 26, 2012 9:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261
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
Post #1388675
CELKO
Posted Monday, November 26, 2012 6:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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
Luis Cazares
Posted Monday, November 26, 2012 8:05 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 960, Visits: 1,923
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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
				 
					
				
						
								

							

							
					
				
						

						
		

							
		
				Post #1388912
				
		
							
		
				

								

									        
									
								

							

							
		
				
						

							
		
		
			
			
	    		      	
		

							
		
				 
				
				
				

								

									
Ajay.Kedar

									
								

							

							
			
				
							
			
				

								

									  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: 200, 
				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
    
		
      
11 posts, Page 1 of 2
12»»
    		
	



	








	

		

			

				

					

						Permissions
						Expand / Collapse
					

				

						
			

			
		

				
	

	

		
		
	











 






    
 





					
			 

		

	

	

		

			 
			
				

					Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.