updating a column in a table only if after the update it won't be negative and identifying all updated rows

Question

Hello all,

I need some help with a SQL query. Here is what I need to do. I'm lost on a few aspects as outlined below.

I've four relevant tables:

Table A has the price per unit for all resources. I can look up the price using a resource id.

Table B has the funds available to a given user.

Table C has the resource production information for a given user (including the number of units to produce everyday).

Table D has the number of units ever produced by any given user (can be identified by user id and resource id)

Having said that, I need to run a batch job on a nightly basis to do the following:

a. for all users, identify whether they have the funds needed to produce the number of resources specified in table C and deduct the funds if they are available from table B (calculating the cost using table A).

b. start the process to produce resources and after the resource production is complete, update table D using values from table C after the resource product is complete.

I figured the second part can be done by using an UPDATE with a subquery. However, I'm not sure how I should go about doing part a. I can only think of using a cursor to fetch each row, examine and update. Is there a single sql statement that will help me avoid having to process each row manually? Additionally, if any rows weren't updated, the part b. SQL should not produce resources for that user.

Basically, I'm attempting to modify the sql being used for this logic that currently is in a stored procedure to something that will run a lot faster (and won't process each row separately).

Please let me know any ideas and thoughts.

Thanks! - Azeem

Answer 1

Without the schema it's hard to think precisely about it, but, basically you should do something like this:

 update B b
   set amount = amount - *subquery*
   where (amount - *subquery*) > 0

being subquery the amount of money to spend by that user.

 select sum(a.priceperunit * c.units)
 from C c join A a on (a.productid = c.productid)
 where c.userid = b.userid

Edit: You should put part a and b into a transaction.

Answer 2

OK I made some guesses about structurehere (and you would want this all in one transaction)

DECLARE @outputTable (userid int)

UPDATE b
    SET amount = amount - newcharges
    OUTPUT inserted.userid INTO @outputTable
FROM tableb b
join 
    (SELECT c.userid, sum (a.amount) AS newcharges FROM tablea a
    JOIN tablec c 
        ON a.productid =c.productid
    WHERE c.date = getdate()
    GROUP BY c.userid) a1
        ON b.userid = a1.userid
WHERE b.amount >= newcharges

UPDATE d
SET units = units + c.units
FROM tabled d
JOIN tablec c ON c.unitid = d.unitid
JOIN @userid u ON c.userid = u.userid
where c.date = getdate()

The derived table woudl be replaced by what ever query you need to sum up the charges that haven't been subtracted yet from tableb:

    (SELECT c.userid, sum (a.amount) AS newcharges FROM tablea a
    JOIN tablec c 
        ON a.productid =c.productid
    WHERE c.date = getdate()
    GROUP BY c.userid) a1

Actually you might also want to condsider what to do if the amount would put the user in a negative amount status. How will you pick up that data later when he has more money or should the record from c be marked as not sent or deleted entirely or what? Personally I think a viable alternative woudl be to check the amount available at the time the record is inserted in c and not insert it if there is no money available. This would have the benefit of telling the user it won't process at the time he is working with the data.