Update All Columns in a Table

Question

Update All Columns in a Table

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
October 4, 2011 at 5:15 pm

#249248

Is there a simple way to update All columns in a single table? Other than selecting the column name from the syscolumns table and writing an update statement with that.
I have a table that is filled from an old Unix system, before I perform any ETL work I want to set all rows in all columns to NULL, if that column/row is storing a blank space. Only way that I know how is to perform an Update and set the data to NULL where that column = ''
Thanks
Andrew SQLDBA

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

AndrewSQLDBA (10/4/2011)
Is there a simple way to update All columns in a single table? Other than selecting the column name from the syscolumns table and writing an update statement with that.
I have a table that is filled from an old Unix system, before I perform any ETL work I want to set all rows in all columns to NULL, if that column/row is storing a blank space. Only way that I know how is to perform an Update and set the data to NULL where that column = ''
Thanks
Andrew SQLDBA

other than creating a command to do it for you by building it from the syscolumns as you mentioned , nope, there is no easy way.

the UPDATE command requires each column that will be updated to be explicitly named.

creating the required command is simple, though...but you are right, there are no shortcuts.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Thanks Buddy

I was hoping someone may have known of something.

I guess I will stick with doing it the hard way then.

Have a good one.

Andrew SQLDBA

Jared SSC Guru Points: 61793 More actions · Answer 3

It may help to right-click on the table -> Script Table As -> Update To -> New query editor window.

At least this will generate all of the text you need.

Thanks,

Jared

Jared
CE - Microsoft

Paul Bradshaw SSC Eights! Points: 949 More actions · Answer 4

You can very easily generate SQL to do the updates, to save you a lot of typing. You can whip up something like this very quickly:

SELECT 'UPDATE [Name] SET ' + COLUMN_NAME + ' = NULL WHERE + ' + COLUMN_NAME + ' = '' '''

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Name'

AND IS_NULLABLE = 'YES' AND DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar')

Just replace "Name" with the name of your table, and run. Copy the output into a SQL Server Query Manager window, and run it. Volia!

The above will update every column of every row in the table to be NULL where the column contains exactly one space.

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

Paul Bradshaw (10/5/2011)
You can very easily generate SQL to do the updates, to save you a lot of typing. You can whip up something like this very quickly:
SELECT 'UPDATE [Name] SET ' + COLUMN_NAME + ' = NULL WHERE + ' + COLUMN_NAME + ' = '' '''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Name'
AND IS_NULLABLE = 'YES' AND DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar')
Just replace "Name" with the name of your table, and run. Copy the output into a SQL Server Query Manager window, and run it. Volia!
The above will update every column of every row in the table to be NULL where the column contains exactly one space.

That query will work for the OP but it will not find records with exactly one space. sql sees any string as equal regardless of the number of spaces. in other words '' = ' ' in sql.

Proof:

create table #CharTest

(

TestKey int identity,

val varchar(10)

)

insert #CharTest (val)

values

(''),

(' '),

(' ')

select * from #CharTest where val = ''

select * from #CharTest where val = ' '

_______________________________________________________________

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/

Jared SSC Guru Points: 61793 More actions · Answer 6

Sean Lange (10/5/2011)
sql sees any string as equal regardless of the number of spaces. in other words '' = ' ' in sql.

This is ONLY true if there are no other characters in the field.