情况就是这样。我有两个表,像这样:
TABLE (haveToBuy):
+--------------------+
| product | quantity |
+--------------------+
| cake | 3 |
| egg | 2 |
| milk | 5 |
+--------------------+
TABLE (alreadyBough):
+--------------------+
| product | quantity |
+--------------------+
| cake | 1 |
| egg | 0 |
| milk | 5 |
+--------------------+
现在我想更新TABLE (alreadyBough)的数量值,所以它看起来像这样:
TABLE (haveToBuy):
+--------------------+
| product | quantity |
+--------------------+
| cake | 3 |
| egg | 0 |
| milk | 5 |
+--------------------+
TABLE (alreadyBought):
+--------------------+
| product | quantity |
+--------------------+
| cake | 1 |
| egg | 2 |
| milk | 5 |
+--------------------+
(2个鸡蛋从TABLE (haveToBuy)移动到TABLE (alreadyBought))。查询会是什么样子?如何修复这个查询:
$query = mysql_query ("INSERT INTO alreadyBought (quantity) SELECT quantity FROM haveToBuy WHERE product='$productID' ");
我认为你应该这样写查询
UPDATE alreadybought
SET quantity= (
SELECT quantity FROM haveToBuy WHERE product="egg"
)
WHERE product="egg"
可以触发另一个更新查询,将haveToBuy
表中的值设置为0
要得到两个值的和,我可以给你一个下面的例子,你可以根据你的需要修改它
select sum(quantity)+sum(newvalue) from message where product="egg"
也可以不使用sum
select quantity+newvalue from message where product="egg"
在使用类似这样的查询时要小心,因为您需要在提交查询之前验证所有值,并确保数据类型相同