#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0

    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.

    Table example:

    Amount_requested: 500.00
    Amount_approved:
    Approval_Status: 'Y'

    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    put away your money

    you just about had it all by yourself
    Code:
    update yourtable
       set amount_approved = amount_requested
      where approval_status = 'Y'
    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

    if, however, amount_approved can sometimes be different from amount requested, then forget i asked

    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0
    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!

IMN logo majestic logo threadwatch logo seochat tools logo