May 2, 2012 at 6:12 pm
I'm using SQL Server Management Studio Express to manage a remotely hosted MSSQL 2005 database.
I have a column in "dbo.cases" table, "Title," that has case titles. I want to go into each record, find every instance of "Assoc." and replace it with "***'n."
My query looks like this:
UPDATE dbo.cases
SET Title = Replace(Title,'Assoc.','***''n')
When I run the query, the result is "4298 row(s) affected," yet the values don't actually get replaced. And we're really talking about just 55 records, so should it be saying it's affecting ALL the rows?
Any ideas where I might be going wrong?
Thanks!
May 2, 2012 at 6:27 pm
Answering my own question: Turns out I needed a WHERE statement with a wildcard so the query could find the rows that contained the (apparently obscene) word I was trying to change.
May 3, 2012 at 12:58 am
the reason why I always start coding the where clause when typing an update query.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 3, 2012 at 8:19 am
Even before the where clause when writing a delete or update query I write
begin transaction
rollback transaction
That way I can run the code without fear of anything going horribly wrong until I know my code is correct. I can check row counts and even run selects inside my transaction to view the changes that are not yet committed.
_______________________________________________________________
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/
May 3, 2012 at 10:58 am
Yep...I learned that lesson the hard way last year, and now all of my code starts with BEGIN TRANSACTION, and COMMIT TRANSACTION / ROLLBACK TRANSACTION at the end of my scripts when doing updates to PROD systems.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply