CODEWITHSUNDEEP
mysqlsql
mark
mark

Reputation: 959

Conditional update query

I have the following query

UPDATE users_ SET equity=equity-100 WHERE account_id=1

The update has to be done only if the user has sufficient funds, i.e. equity is >= 100. How can it be done?

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use a where clause:

UPDATE users_
    SET equity = equity - 100
    WHERE account_id = 1 AND equity >= 100;

For extra protection, MySQL finally supports check constraints. So in the most recent versions, you can do:

alter table users_ add constraints chk_users_equity
    check (equity >= 0);

This will not allow negative values. Actually, if equity is an integer, then you can declare it to be unsigned and have the same effect.

Upvotes: 1

Related Questions