SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update null to unknown in a table for all the columns


Update null to unknown in a table for all the columns

Author
Message
Kumara1011
Kumara1011
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 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.
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80762 Visits: 17931
You'll be looking for the COALESCE function.

John
anthony.green
anthony.green
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63272 Visits: 8598
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?



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
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


WolfgangE
WolfgangE
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3457 Visits: 804
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
Kumara1011
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 115
Datatype for all the columns is nvarchar only.

we had such a requirement.
WolfgangE
WolfgangE
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3457 Visits: 804
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
Sean Lange
SSC Guru
SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)SSC Guru (149K reputation)

Group: General Forum Members
Points: 149727 Visits: 18575
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.

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)
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96208 Visits: 21206
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search