Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2008-10-02
6
Medium Priority
?
296 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
5 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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

578 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