Solved

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

Posted on 2008-10-02
6
287 Views
Last Modified: 2012-05-05
Hello,

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
WATCH LIST      WATCHLIST

On this particular case I dont have a best guess to offer... Sorry! Please note we are running SQL 2000.
0
Comment
Question by:smyers051972
6 Comments
 
LVL 8

Expert Comment

by:jtdebeer
ID: 22625033
Try

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.
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22625217
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:
#VALUE!

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!
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22633703
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.
0
 
LVL 10

Accepted Solution

by:
AaronAbend earned 250 total points
ID: 22634051
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,"'")
0
 
LVL 1

Author Comment

by:smyers051972
ID: 22636064
Thank you!

This solved the problem, very much Appreciated!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now