Razib Al Mamun Razib Al Mamun - 19 days ago 5
SQL Question

Increment quantity but not more then main quantity field

I have a mysql table

proforma_invoice_items


-----------------------------------------
id | qty | total_invoice_qty |
-----------------------------------------
452 | 50 | 45 |


Now, i want to increment
total_invoice_qty
value with
10
, but not maximum
qty
value
50


I have tried bellow query :

update proforma_invoice_items a set a.total_invoice_qty = a.total_invoice_qty+10 where a.id = 452 and a.total_invoice_qty < a.qty


and result is :

-----------------------------------------
id | qty | total_invoice_qty |
-----------------------------------------
452 | 50 | 55 |


My expected result is maximum
qty
value
50
like

-----------------------------------------
id | qty | total_invoice_qty |
-----------------------------------------
452 | 50 | 50 |

Answer

You can use LEAST():

With two or more arguments, returns the smallest (minimum-valued) argument.

UPDATE proforma_invoice_items a
SET a.total_invoice_qty = LEAST(a.total_invoice_qty + 10, a.qty)
WHERE a.id = 452