update remaining balance to 0 if payment is greater than remaining balance

Question

using this query i can easily deduct the payment to the remaining balance

let say that my rem_balance is 3000 and my payment is 5000 so basically the change would be 2000.

UPDATE tbl_users SET rem_balance = (rem_balance - '5000') WHERE user_id = '2017001002'

but since i use this query what happen is the rem_balance updates to -2000 what i wanted to achieve is that if payment is > rem_balance, rem_balance becomes 0 and it will separate the change.

refere link https://stackoverflow.com/questions/15001217/update-mysql-with-if-condition — Devsi Odedra, Nov 16 '17 at 10:32

Radim Bača · Accepted Answer · 2017-11-16T10:52:16.657

2

Something like this should work

UPDATE tbl_users 
SET rem_balance = (CASE WHEN rem_balance < 5000 THEN 0 ELSE rem_balance - 5000 END)
WHERE user_id = '2017001002'

Please be aware of the fact that you use implicit conversion in your SQL ('5000' instead of 5000). It is a bad habit and it can harm performance of your queries in certain cases. For example, if tbl_users.user_id is integer as well then the update will be unable to use indexes to search the row with the specific user_id and it will do a sequential scan which is very slow.

edited Nov 16 '17 at 10:52

answered Nov 16 '17 at 10:33

Radim Bača

10,646
1
19
33

is there a possibility on this query to get the change of rem_balance < 5000 like return the remaining value?. – Lion Smith Nov 16 '17 at 10:36
@LionSmith I'm not sure what you are asking. You want the remaining balance to be returned? – Radim Bača Nov 16 '17 at 10:39
nahh.. this is exactly what i ask.. my comment is just a question based on your answer.. – Lion Smith Nov 16 '17 at 10:40
@LionSmith I think that it is not possible in one query. You need to run `SELECT` after update to retrieve the new `rem_balance` value. – Radim Bača Nov 16 '17 at 10:51

update remaining balance to 0 if payment is greater than remaining balance

1 Answers1