Updating two columns given condition in 1 query

Question

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

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.

How to prevent SQL injection in PHP?

edited May 23 '17 at 11:56

Community

1
1

answered Jan 04 '13 at 01:26

John Woo

258,903
69
498
492

thanks, it works with little change. I modified if statement status=IF(balance=0,2,status). I guess balance=balance-qty perform first and if statement. – user1084885 Jan 04 '13 at 18:26
user input is forced to be numeric, so no need to worry about SQL Injection – user1084885 Jan 04 '13 at 18:35

score 0 · Answer 2 · answered Jan 04 '13 at 01:25

0

Yes; you can write something like:

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

answered Jan 04 '13 at 01:25

ruakh

175,680
26
273
307

Updating two columns given condition in 1 query

2 Answers2