Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-02
6
Medium Priority
?
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 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

AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

704 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