Wednesday, March 28, 2012
Replacing blank records with text
I'm in a bit of a jam here and will appreciate any help.
I need the SQL code to replace a record if the record is empty.
For instance, I have about 7 columns containing over 40K records. In the firstname field, some records are blank. I need to replace all the blank firstname fields with this: 'now invalid' (without the quotes)
What would be the best way to achieve this?
Thanks
newbie:oUpdate [YourTable]
set FirstName = isnull(FirstName, 'now invalid'),
LastName = isnull(LastName, 'now invalid'),
etc...|||Thanks alot for your response blindman.
I didnt add that for the blank firstname fields I want to replace, all their lastnames contain valid data which I do not want to replace. all the lastnames are 'Design Professional'
So i modified the sql like this:
Update [My Table]
set FirstName = isnull(FirstName, 'Now Invalid')
where lastname = 'Design Professional'
I think the above query has a problem because it generated an error saying:
saying 'wrong number of arguements used with function in query expression isnull(FirstName, 'now invalid')
can you help with this?
Thank you very much|||Thanks alot for your response blindman.
I didnt add that for the blank firstname fields I want to replace, all their lastnames contain valid data which I do not want to replace. all the lastnames are 'Design Professional'
So i modified the sql like this:
Update [My Table]
set FirstName = isnull(FirstName, 'Now Invalid')
where lastname = 'Design Professional'
I think the above query has a problem because it generated an error saying:
saying 'wrong number of arguements used with function in query expression isnull(FirstName, 'now invalid')
can you help with this?
Thank you very much|||Are you using SQL Server or MS Access? The isnull() function is different in TSQL than in Access VB.|||If you are in Access look up NZ in help. Same thing as the T-SQL ISNull basically. IsNull in Access means something totally different to IsNull in T-SQL.|||Thanks a lot for the suggestions. I'm using MS Access.
Tuesday, March 20, 2012
rephrasing my quick SQL question......
i really appreciate the responses i received about my sql question. i think, however, that i wasn't really making my question clear and hope it's not too annoying to try one more time....
machine A has an instance of sql server running and it is listed under enterprise manager as "(local)(windows nt)"
now...
if i go to machine B, or C, or D or whatever and try to make a new registration...the drop down list of available sql servers on our network includes one called "sqlReports" - and that is the one which on the actual machine it resides on, is called "(local)(windows nt)" - so...if it isn't called "sqlReports" on the machine it resides on.....how is it coming up with that name on all the DROP DOWN LISTS of available sql servers on all the other machines?
and how can i rename "(local)(windows nt)" to something else??
Is the machine name sqlReports? If that isn't it, are you running active directory?|||
i THOUGHT the machine name was something else but after your post i went and double checked and it IS sqlReports.
mystery solved.
thanks