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


Update Table - By Looping Through Field Name


Update Table - By Looping Through Field Name

Author
Message
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 457
HI,
Is it possible to achieve the following ;

I have a table with say ... 200 Fields and I want to check each field for a value and possibly update if a match is found.

something like...


FOR EACH "FieldWithinMyTable" as FieldChecker
IF FieldChecker = 'FoundIt' then
update FieldChecker with new fielddata
ENDIF
NEXT

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59134 Visits: 17947
SteveEClarke (9/16/2013)
HI,
Is it possible to achieve the following ;

I have a table with say ... 200 Fields and I want to check each field for a value and possibly update if a match is found.

something like...


FOR EACH "FieldWithinMyTable" as FieldChecker
IF FieldChecker = 'FoundIt' then
update FieldChecker with new fielddata
ENDIF
NEXT



Would need a lot more details about your tables and such before anybody can offer much advice here. Please take a few minutes and read the first link in my signature. I am pretty sure we can come up with a solution that doesn't require looping but hard to know for sure until we have some details to work with.

_______________________________________________________________

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)
Amit Raut
Amit Raut
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1195 Visits: 342
It is possible to achieve. Not sure what values you would be comparing. Please provide some more details.
SteveEClarke
SteveEClarke
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 457
Okay -
So I have a basic table - 100 Columns - ALL VALUES, when populated, there are NULL values entered instead of 0 ( zero ).

So want to scan the fields and replace NULL with 0 (zero)

update BasicTable
for count = 1 to maximumnumberoffields
if fieldname[count] is null
set fieldname[count] = 0
endif
next

something like that !?

________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59134 Visits: 17947
SteveEClarke (9/16/2013)
Okay -
So I have a basic table - 100 Columns - ALL VALUES, when populated, there are NULL values entered instead of 0 ( zero ).

So want to scan the fields and replace NULL with 0 (zero)

update BasicTable
for count = 1 to maximumnumberoffields
if fieldname[count] is null
set fieldname[count] = 0
endif
next

something like that !?


No. You will have to either code a big long list or use dynamic sql for this. You could use some dmvs to help build your dynamic sql.

See if this might help get you started. All you would need to do is add the UPDATE TableName and any where predicates. This would build all the column updates for you.


select sc.name + ' = isnull([' + sc.name + '], 0), '
from sys.objects so
join sys.columns sc on so.object_id = sc.object_id
where so.name = 'YourTableNameHere'
--and any other filters needed



_______________________________________________________________

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)
Leonard Rutkowski
Leonard Rutkowski
Mr or Mrs. 500
Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)Mr or Mrs. 500 (526 reputation)

Group: General Forum Members
Points: 526 Visits: 1213
A simple update would take care of this.
Example 1 :
Update 1 column:
UPDATE TableName
SET Column1 = 0
WHERE Column1 IS NULL

Example 2:
UPDATE TableName
SET Column1 = COALESCE(Column1, 0)


Example 3:
UPDATE TableName
SET Column1 = CASE WHEN Column1 IS NULL THEN 0 ELSE Column1 END

Example 4:
UPDATE TableName
SET Column1 = COALESCE(Column1, 0),
Column2 = COALESCE(Column2, 0),
Column3 = COALESCE(Column3, 0) ... etc.

hth,
Leonard
C Hrushi
C Hrushi
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 49
This might hep for n number of columns :


*********************************************************************************************
DECLARE @Q NVARCHAR(4000),
@CName VARCHAR(255)

DECLARE ColCur CURSOR
FOR
SELECT C.name as Column_name
FROM sys.columns C
LEFT JOIN sys.tables t
on C.object_id = t.object_id
WHERE t.name = 'Your_table'

OPEN ColCur

FETCH NEXT FROM ColCur INTO @CName


WHILE(@@FETCH_STATUS =0)
BEGIN
SET @Q = 'UPDATE Your_Table SET '+@CName+' = NULL WHERE '+@CName+' ISNULL '
PRINT @Q

FETCH NEXT FROM ColCur INTO @CName

END

CLOSE ColCur
DEALLOCATE ColCur
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59134 Visits: 17947
C Hrushi (9/17/2013)
This might hep for n number of columns :


*********************************************************************************************
DECLARE @Q NVARCHAR(4000),
@CName VARCHAR(255)

DECLARE ColCur CURSOR
FOR
SELECT C.name as Column_name
FROM sys.columns C
LEFT JOIN sys.tables t
on C.object_id = t.object_id
WHERE t.name = 'Your_table'

OPEN ColCur

FETCH NEXT FROM ColCur INTO @CName


WHILE(@@FETCH_STATUS =0)
BEGIN
SET @Q = 'UPDATE Your_Table SET '+@CName+' = NULL WHERE '+@CName+' ISNULL '
PRINT @Q

FETCH NEXT FROM ColCur INTO @CName

END

CLOSE ColCur
DEALLOCATE ColCur


No need for a cursor for something this simple. You can easily use the code I posted. All you need to do is add 'Update MyTable set' + [query results]

You could use STUFF to build up the comma delimited list of values, the values in this case are the column update sql strings. Then execute the string.

_______________________________________________________________

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)
Amit Raut
Amit Raut
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1195 Visits: 342
Sean Lange (9/17/2013)
C Hrushi (9/17/2013)
This might hep for n number of columns :


*********************************************************************************************
DECLARE @Q NVARCHAR(4000),
@CName VARCHAR(255)

DECLARE ColCur CURSOR
FOR
SELECT C.name as Column_name
FROM sys.columns C
LEFT JOIN sys.tables t
on C.object_id = t.object_id
WHERE t.name = 'Your_table'

OPEN ColCur

FETCH NEXT FROM ColCur INTO @CName


WHILE(@@FETCH_STATUS =0)
BEGIN
SET @Q = 'UPDATE Your_Table SET '+@CName+' = NULL WHERE '+@CName+' ISNULL '
PRINT @Q

FETCH NEXT FROM ColCur INTO @CName

END

CLOSE ColCur
DEALLOCATE ColCur


No need for a cursor for something this simple. You can easily use the code I posted. All you need to do is add 'Update MyTable set' + [query results]

You could use STUFF to build up the comma delimited list of values, the values in this case are the column update sql strings. Then execute the string.


I agree!
yungmunk
yungmunk
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 80
Hello, I'm sorry to resurrect such an old post but I'm trying to do basically the same thing the original poster was. Your code looks very simple and easy to use but I'm not sure how an UPDATE would fit in to this. Could you provide an example?
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