CODEWITHSUNDEEP
mysqldecrement
Beep
Beep

Reputation: 2823

decrement in MySQL to stop at 0

hey guys I have got a MySQL query that reduces stock every button click,

Query

UPDATE Animals 
   SET Stock = Stock - 1 
 WHERE Specie ='{1}' and Country ='{0}'

problem is I wish for it to stop at 0 and not go into negative is there a way to do this?

Upvotes: 1

Views: 806

Answers (5)

Roopak A Nelliat
Roopak A Nelliat

Reputation: 2541

In cases where you want to decrement the inventory by more than one -

'{2}' here, refers to the quantity to be decremented.

UPDATE Animals
  SET Stock = Stock - '{2}'
  WHERE 
     Specie ='{1}' and Country ='{0}' 
     AND Stock - '{2}' >= 0

Upvotes: 0

Marcus Adams
Marcus Adams

Reputation: 53840

To prevent over-ordering, you might want to check to see if there's actually stock, hold it in reserve, then make your decision, by using a transaction:

START TRANSACTION;
SELECT Stock
  FROM Animals
  WHERE Specie ='{1}' and Country ='{0}' AND Stock > 0
  FOR UPDATE;

If you didn't get a record, the stock is <= 0. If you get a record, you've got the remaining stock count.

Now update it:

 UPDATE Animals SET Stock = Stock - 1 WHERE Specie ='{1}' and Country ='{0}';
 COMMIT;

Otherwise,

 ROLLBACK;

You'll want to do this over the same session (connection).

Upvotes: 1

Kneel-Before-ZOD
Kneel-Before-ZOD

Reputation: 4221

This should work:

 
 "UPDATE Animals SET Stock = Stock - 1 WHERE Specie ='{1}' and Country ='{0}' and Stock >0"

Upvotes: 3

Wrikken
Wrikken

Reputation: 70490

... SET Stock = IF(Stock < 1, Stock, Stock -1)...

Upvotes: 0

zerkms
zerkms

Reputation: 254926

UPDATE Animals
   SET Stock = Stock - 1
 WHERE Specie ='{1}' and Country ='{0}' AND Stock > 0

So restrict the row to have Stock > 0 explicitly

Upvotes: 6

Related Questions