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
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

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

John
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10052 Visits: 6321
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
When a question, really isn't a question - Jeff Smith
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


WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 792
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
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

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

we had such a requirement.
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 792
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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25885 Visits: 17511
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16299 Visits: 19062
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