CODEWITHSUNDEEP
phpmysqlsql
user1084885
user1084885

Reputation: 77

Updating two columns given condition in 1 query

I have a column name 'balance' & 'status'. I got the Qty from users input.
What I want to perform is update balance (balance-qty)
and after updating balance if balance is 0 then I want to change status to 2.
Is it possible to perform these 2 operations in one query?

Thanks in advance.

Upvotes: 0

Views: 178

Answers (2)

John Woo
John Woo

Reputation: 263693

Yes, you can do that it one query, all need need to do is to use InLine IF statement.

UPDATE tableName
SET balance = balance - qty,
    status = IF(balance - qty = 0, 2, status)
// WHERE condition here (if any)...

and since you have mentioned that the Qty came from the user, please take a look at the article below to learn how to prevent from SQL Injection. By using PreparedStatements you can get rid of using single quotes around values.

Upvotes: 1

ruakh
ruakh

Reputation: 183251

Yes; you can write something like:

UPDATE ...
   SET balance = ...,
       status = CASE WHEN ... = 0 THEN 2 ELSE status END
 WHERE ...
;

Upvotes: 0

Related Questions