September 16, 2013 at 4:50 am
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.
September 16, 2013 at 8:31 am
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 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/
September 16, 2013 at 12:58 pm
It is possible to achieve. Not sure what values you would be comparing. Please provide some more details.
September 16, 2013 at 1:59 pm
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.
September 16, 2013 at 2:37 pm
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 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/
September 16, 2013 at 2:59 pm
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
September 17, 2013 at 1:14 am
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
FROMsys.columns C
LEFT JOIN sys.tables t
on C.object_id = t.object_id
WHEREt.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
September 17, 2013 at 8:18 am
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
FROMsys.columns C
LEFT JOIN sys.tables t
on C.object_id = t.object_id
WHEREt.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 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/
September 17, 2013 at 12:28 pm
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
FROMsys.columns C
LEFT JOIN sys.tables t
on C.object_id = t.object_id
WHEREt.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!
March 2, 2015 at 5:11 pm
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?
March 2, 2015 at 5:13 pm
Sean Lange (9/16/2013)
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
I apologize, I should've hit quote instead of reply
March 3, 2015 at 7:12 am
yungmunk (3/2/2015)
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?
Assuming you are trying to do the same as the OP just add the string literal I posted at the beginning of the sql variable.
You might also want to take a look at this article to generate the comma separated list of columns. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
_______________________________________________________________
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/
March 3, 2015 at 9:24 am
Sean Lange (3/3/2015)
yungmunk (3/2/2015)
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?Assuming you are trying to do the same as the OP just add the string literal I posted at the beginning of the sql variable.
You might also want to take a look at this article to generate the comma separated list of columns. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
I'm sorry but does this code change the data within the table or the column names? Also I'm not sure what you mean by the sql variable. I don't see a variable declared. Sorry, I'm brand new to dynamic sql.
March 4, 2015 at 7:07 am
yungmunk (3/3/2015)
Sean Lange (3/3/2015)
yungmunk (3/2/2015)
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?Assuming you are trying to do the same as the OP just add the string literal I posted at the beginning of the sql variable.
You might also want to take a look at this article to generate the comma separated list of columns. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
I'm sorry but does this code change the data within the table or the column names? Also I'm not sure what you mean by the sql variable. I don't see a variable declared. Sorry, I'm brand new to dynamic sql.
Dynamic sql is not always a simple concept to grasp. Let's take a step back. What exactly are you trying to do?
_______________________________________________________________
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/
March 30, 2015 at 4:50 pm
CELKO (3/5/2015)
Is it possible to achieve the following ;
Yes, and you can put a sharp stick in your eyes, too. 🙁 You will need to flatten the rows (rows are not records) , and convert this SQL into a 1950's COBOL file. It will run 2-3 orders of magnitude slower, have no data integrity, etc.
I have a table with say ... 200 Fields [sic] and I want to check each field [sic] for a value and possibly update if a match is found.
1. Columns are not fields; this is a fundamental concept in RDBMS :w00t:. Your mindset is locked into 1950's punch cards and mag tape files.
2. Your next error is not knowing that SQL is a declarative language. We do not use loops and control flow structures in this language.
something like... <<
Why did you invent a personal language instead of showing us the SQL or T-SQL code you tried? Or have you even tried anything before asking for help?
Look up normalization. Your vague narrative seems to say that all columns (NOT fields) are in some way interchangeable. But in RDBMS, each column is a district attribute, with a different meaning and possibly measured on totally different scale.
Sorry for the delay, I tried to put some of my code in here (the correct way) and the firewall blocked the entire site. I got that resolved but was asked not to use "sql injection" on this site anymore :/
Anyway I did find a solution to my problem but am unable to post it because the firewall will once again block this site and I need it for the forums and stairways.
Thanks for your help, much appreciated
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply