We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

SQL - set certain records to nothing - not null or zero - as if no data was in the columns for selected rows

pabby061203
pabby061203 asked
on
Medium Priority
803 Views
Last Modified: 2010-07-27
HI ,

I am trying to update certain rows of a dbase and set certain columns in the rows to nothing. Someone has entered zeros in these columns and I need to update the table and set the rows back to empty (not null or zero)


have tried "  " and '  '  but getting  syntax errors. What can I use to set the rows back to empty as if there was no data there ?

thanks.
Comment
Watch Question

Commented:
UPDATE dbo.yourtable
SET yourfield=NULL
WHERE yourfield=0



-Paul.
>>>>>>I need to update the table and set the rows back to empty (not null

"nothing" IS null. So if you want to set tjose rows to nothing 0 can't be used as it is int, " " cannot be used as it is varchar... and it is not "nothing".  SO it shld be null

so the query shld be
update <tablename>
set <column>=null WHERE <column>=0
                         
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>What can I use to set the rows back to empty as if there was no data there ?<<
If you do not want to use Null, than try this:

Update YourTableName
Set YourColumnName = ''
Where YourColumnName = '0'
CERTIFIED EXPERT
Top Expert 2012

Commented:
Also, the following question is considered abandoned, please attend to it:
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21709703.html

Author

Commented:
Hi again and thanks.


I have found that it is slightly more complicated now. There are complete rows in this table that have been populated with a zero in each cell. I need to identify where the complete rows of zeros are - there may be other rows that have one or two zeros in them - I don't need to update those rows - it is only where a row is totally populated with zeros -not partially populated.


The column names are  Question1, question2, question3, question4 and question5. Tablename is tblanswers.
any ideas ?

thanks again.




Author

Commented:
Hi ,

I have solved this one myself ,  I used  

update tblanswers
set question1 = ' ' where question1 = 0 and question2 = 0 and question3 = 0 and question4 = 0 and question5 = 0


then I ran the same statement to set question2 = ' ' where question1 = 0 etc etc etc etc

thanks
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>I have solved this one myself <<
Great! Now please close the question.

Author

Commented:
HI.

I would like to close this question but am not seeing where to do this . Can you point me in the right direction,

thanks
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>Can you point me in the right direction<<
Sure.  It is all covered in the EE Help:

What are my choices?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi67

Accept an Expert's Comment as the Answer http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi68
Accept Comments from more than one Expert (a Split) http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi69
Request a refund because you answered your own question (Refund/PAQ) http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70 
Request a refund because no one answered your question (Delete) http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi71 
Abandon your question and leave the mess for someone else to clean up http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi78
Commented:
Closed, 125 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.