updating a column in a table only if after the update it won't be negative and identifying all updat
- by Azeem
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
