• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Need assistance for an update statement on live DB (MSSQL 2000)


I am at am impasse with a update statement I am trying to create so I thought I would just break down and ask for help!

What I am trying to do is create an update statement to modify our casino database in order to correct a LOT of typo's in our business source code field which is known as sourcecode in the database.  I have made a query that basically says is this sourcecode = X change it to Y but then I have to run it EACH time on EACH sourcecode to change and what I was wanting to do was an easier way, I want to make a loop and list EACH sourcecode that needs to be changed perhaps a select statement and have it change it to the proper format.  Sorry to sound like I am running off but I dont know else to explain it. I dont have any good snippets to submit because I really cant figure out how to put it together in my head.

Here is an example of some changes that should be made although there are 100's more:
IS                             should be
GOLD307                      Blank
amylwaz@ao      Blank

On this particular case I dont have a best guess to offer... Sorry! Please note we are running SQL 2000.
1 Solution

Update TableName Set ColoumnName = 'SOULD BE' where ColoumnName = 'IS'

This would fix the first, if you have the changes in an excel spreadsheat put old value in colA new value in colB
ColC use a fromula like this:

=concatenate("Update TableName Set ColoumnName = '"+A1+"' where ColoumnName = '"+b1+"'"

Then populate the sheet, copy the update statements and pass them against the database.

Bob's your uncle.
smyers051972Author Commented:
Hi Bob

Thank you so much for the fast reply.

I followed to the best I could understand what you said and am getting an error, you are correct they supplied me all the changes in an excel spreadsheet.

Here is what I entered into excel:  
=CONCATENATE("Update CDS_PLAYER Set sourcecode = '"+A3+"' where sourcecode = '"+B3+"'")

The error I get is:

All the data starts at A3 displaying the old results I previously supplied and B3 is the new results, they had ??'s in there because they werent sure and the word blank was supposed to be empty so I fixed all that but none the less I am still getting that error. What did you mean by "Then populate the sheet"  I guess that would be secondary to the above.

Thanks again!
What format do you have your "IS" and "SHOULD BE" in?  Are they in a table somewhere?

Also #Value is probably not an error but possibly just stating that the data can't be displayed in the current cell width.  try expanding the cell.  Also, if any of your data contains single quotes, that will have to be dealt with and currently isn't.
The syntax for concatenate in Excel is to use Commas between elements, not + signs (which is the concatenate in SQL).
CONCATENATE("Update CDS_PLAYER Set sourcecode = '",A3,"' where sourcecode = '",B3,"'")
smyers051972Author Commented:
Thank you!

This solved the problem, very much Appreciated!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now