Execute OPERATION (plus, minus, multiply, divide) in Update SQL

Question

How to execute plus, minus, multiply or divide operation in SQL to the selected table and row?

Below is example code where I manually minus original quantity to create a new quantity and update into the selected row:

$idArr = $_POST['checkboxId'];

foreach($idArr as $index=>$value)
{
    $id = mysql_real_escape_string($value);

    // Get Quantity from this item id
    $sql = "SELECT quantity FROM items WHERE item_id = '$id'";
    $result = mysql_query($sql);
    $quantity = $row['quantity'];

    // New quantity after minus by 1
    $new_quantity = $row['quantity'] - 1;

    // Update new quantity to this item
    $sql = "UPDATE items SET quantity = '$new_quantity' WHERE item_id = '$id'";
    $result = mysql_query($sql);
}

Is it a practical way to update changes of quantity(integer) in preferred rows? Can I do that with a single update query?

Answer 1

Why not just put the operation in the SQL update query ?

For example, you could have a query such as this one :

UPDATE items SET quantity = quantity - 1 WHERE item_id = '$id'

Or :

UPDATE items SET quantity = quantity + 1 WHERE item_id = '$id'

Great advantage : this is done in a single SQL query (no select, and, then, update) ; which means there will be no problem if two users try to do this at the exact same time : SQL will deal with concurrency, and do one query after the other.

With your initial solution, you could have (if you're pretty unlucky -- but this happens) :

• first user does a select, gets 5 as quantity
• second user does a select, gets 5 as quantity
• first user updates to 4
• second user updates to 4 too... but, there, it should have been updated to 3 !