Solved

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

Posted on 2008-10-02
6
289 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

860 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