Monday, 19 August 2013

Update Value without cursor

Update Value without cursor

I have a table in the database.
Bill
ID Total Paid Status
1 1000 1000 Paid
2 500 400 Part Paid
3 700 0 Unpaid
4 200 0 Unpaid
Now the User pays PAID_AMT -> $900, which i want to distribute such that
my table looks:
ID Total Paid Status
1 1000 1000 Paid
2 500 500 Paid
3 700 700 Paid
4 200 100 Part Paid
It can be easily done using cursor, but i want to avoid cursors.
Is it possible to achieve this using simple update queries with Output
parameters.
Something like this
Update Bill
Set Paid = Total,
Status = 'Paid',
Output PAID_AMT = PAID_AMT - (Total-Paid )
where Total-Paid > PAID_AMT

No comments:

Post a Comment