PHP MySQL Update minus variable without separate SELECT Query

I know there is a way to do this. Basically I have the variable 开发者_高级运维$amount and want to take that away from the column amount in the table stock.

The problem is I normally run a select query find out the value then deduct the variable and run another update query.

What is the syntax to run a single update that will just minus the value.

All help appreciated.

It's as simple as it seems. Parentheses are optional in this example. Be sure to use the appropriate condition in your WHERE clause!

$qry = "UPDATE table SET column = (column - $amount) WHERE somecondition;";

The variable $amount is assumed to be properly escaped already, according to the MySQL API you are using, or better yet, a parameter to a prepared example, as in PDO:

$qry = "UPDATE table SET column = (column - :amount) WHERE somecondition;";
$stmt = $db->prepare($qry);
$stmt->execute(array(':amount' => $amount));

Try something like this:

UPDATE table_name SET amount = amount - $amount WHERE primary_key = value LIMIT 1

If you want to make sure amount doesn't go below zero:

UPDATE table_name SET amount = amount - $amount WHERE primary_key = value AND amount >= $amount LIMIT 1

Out of curiosity are you getting $variable from some other sql select? Like perhaps a parts list or something? It could potentially be much faster to run the entire thing inside a stored procedure or compound select.

If that $variable is static or locally generated disregard.

do you want below?

UPDATE table_name
SET amount = amount -$amount
WHERE primary = id