Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Table - By Looping Through Field Name Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 4:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 5:03 AM
Points: 177, Visits: 417
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.
Post #1494993
Posted Monday, September 16, 2013 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's 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)
Post #1495117
Posted Monday, September 16, 2013 12:58 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: Tuesday, April 1, 2014 6:12 PM
Points: 827, Visits: 342
It is possible to achieve. Not sure what values you would be comparing. Please provide some more details.
Post #1495247
Posted Monday, September 16, 2013 1:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 5:03 AM
Points: 177, Visits: 417
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.
Post #1495267
Posted Monday, September 16, 2013 2:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's 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)
Post #1495281
Posted Monday, September 16, 2013 2:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 160, Visits: 965
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

Post #1495290
Posted Tuesday, September 17, 2013 1:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 20, 2013 12:59 AM
Points: 59, 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
Post #1495385
Posted Tuesday, September 17, 2013 8:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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 Moden's 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)
Post #1495523
Posted Tuesday, September 17, 2013 12:28 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: Tuesday, April 1, 2014 6:12 PM
Points: 827, 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!
Post #1495628
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse