我有一个问题。我想汇总两个 SELECT 值(旧值 + 另一个表中的新值(并像这样更新旧值:
$query = mysql_query ("UPDATE Inventory
SET quantity = (
SELECT quantity
FROM Inventory
WHERE productname='$AwaitedToMOVE'
) +
(
SELECT quantity
FROM Awaited
WHERE OrderName='$Awaited'
)
WHERE productname='$AwaitedToMOVE'; ");
如何计算总和?
如果我正确理解了你的问题,这样的事情应该可以完美地工作。
$query = mysql_query("
UPDATE Inventory iv
SET quantity = quantity + (
SELECT quantity
FROM Awaited aw
WHERE aw.productname = iv.productname
)
WHERE iv.productname = '$AwaitedToMOVE';
");
您应该能够像这样SUM()
您在 2 个子查询中选择的 2 个字段。
$query = mysql_query (
"UPDATE Inventory
SET quantity = (
SELECT SUM(quantity)
FROM Inventory
WHERE productname='$AwaitedToMOVE'
) +
(
SELECT SUM(quantity)
FROM Awaited
WHERE OrderName='$Awaited'
)
WHERE productname='$AwaitedToMOVE'; ");
我会将这些查询分成三个不同的查询,并使用 PHP 来计算总和。
$query_initial_value = mysql_query('SELECT quantity FROM Inventory WHERE productname = "' . $AwaitedToMove . '"');
$query_new_value = mysql_query('SELECT quantity FROM Awaited WHERE OrderName = "' . $Awaited . '"');
$sum = $query_initial_value + $query_new_value;
$query_update = mysql_query('UPDATE Inventory SET quantity = ' . $sum . ' WHERE productname = "' . $AwaitedToMove . '"');
试试这个:
UPDATE Inventory SET Inventory.quantity =
( Inventory.quantity +
(SELECT Awaited.quantity FROM Awaited WHERE Awaited.OrderName = 'Condition') )
WHERE Inventory.productname = 'Condition'