Mysql query to update row and return the new value with the same query

Question

I have this simple query which updates products qty:

$updprqty='UPDATE products set     
available_qty = available_qty - '.$qty.' 
WHERE id = '.$pid.'';    

What else should I add so I can get the new value of available_qty ? Thanks

Answer 1

$updprqty='UPDATE products set     
available_qty = (select available_qty from products where id = ' .$pid . ') - '.$qty.' 
WHERE id = '.$pid.'';  

Introducing alias

$updprqty='update products set available_qty =  x.available_qty from (select available_qty, id from products) - '.$qty.' where id = ' .$pid . ')  as x where id = x.id';