October 19th, 2003, 10:40 AM
Help with SQL 2000
I need some help trying to do something in SQL2000. I am an ASP developer and as such, not the DBA I wish I was also.
My problem is this:
I will simplify it by only putting the relevant part. I need to know how to update a field in all records that have a certain status with the calculated value from another column in that record.
What I need is a way (either a single query - best choice) or a stored_proc with a cursor maybe that would go through these and set amount_approved = amount_requested on all records where approval_status = 'Y'
There could be 50,000 records that meet this criteria, so I am looking for the fastest possible way. I have done it in a cursor but it is slow at best since I am new to cursors and SQL I am probably doing it the hard way.
Any help would be GREATLY appreciated.
Thanks in advance.
Last edited by syruss; October 19th, 2003 at 11:11 AM.
October 19th, 2003, 11:10 AM
put away your money
you just about had it all by yourself
there remains the question of why you even have the "amount_approved" column at all, if it's gonna be equal to the amount requested for all these records
set amount_approved = amount_requested
where approval_status = 'Y'
if, however, amount_approved can sometimes be different from amount requested, then forget i asked
October 19th, 2003, 11:22 AM
Actually just before you replied, I figured it out. I removed that paypal part, but will be glad to honor it if you wish.
The purpose behind this is to make it easier to calculate a lot of other items that go along with this. This was just a small piece of the over table and query.
The reason I asked was because I created the query and it kept failing, so I assumed maybe I was trying to do something backwards. I have been working on this set of about 50 queries for the last 2 days, and my mind is shot. So instead of thinking about it, I kinda gave up for a bit. Then I rethought it and tried again and it worked.
I don't have the originals so not sure why they weren't working, but this one does now and it solved the problem.
It is a fun set of queries to work with as it is related to a play gambling site, and when done needs to be able to work on as many as several million bets a day potentially, so making it fast is important.
The site it is based off of ABSOLUTELY SUCKS. I have the code but the way it works will support about 300 users on the server making 2-10 bets a day is all. The new site will be 100 times that and the old code would just kill the dedicated server.
Thanks for your help, as I am sure I will have more here!