mysql - I want to update the purchases table quantity value from order_item if the order is canceled in php
Get the solution ↓↓↓Please check the below code and please help me the correct syntax to used.
$changeStatusSql = "
UPDATE purchases
SET quantity = (select purchases.quantity+order_item.quantity as q
from purchases,order_item
WHERE id IN ( SELECT purchase_id
FROM order_item
WHERE order_id = {$orderId}))
WHERE id IN ( SELECT purchase_id
FROM order_item
WHERE order_id = {$orderId});";
$connect->query($changeStatusSql);
Please find the below table details
mysql> select id,quantity from purchases;
+----+
expected answer should add the quantities from order_item which match the order_item.purchase_id = purchases.id
Answer
Answer
Solution:
Firstly this seems like redundant data and therefore not something that should really be stored in the database... I'm not sure what you're using the data for but it seems like you could do something better withSUM()
? As that appears to be the intended goal anyway?
Anyway answering the question as asked:
SQL
UPDATE purchases p
JOIN order_item o
ON p.id = o.purchase_id
AND o.id = ?
SET p.quantity = p.quantity + o.quantity;
Additionally you have a variable in your query which should be substituted for a placeholder in a prepared statement as follows:
mysqli
$sql = "
UPDATE purchases p
JOIN order_item o
ON p.id = o.purchase_id
AND o.id = ?
SET p.quantity = p.quantity + o.quantity;
";
$query = $mysqli->prepare($sql);
$query->bind_param("i", $orderId);
$query->execute();
PDO
$sql = "
UPDATE purchases p
JOIN order_item o
ON p.id = o.purchase_id
AND o.id = ?
SET p.quantity = p.quantity + o.quantity;
";
$query = $pdo->prepare($sql);
$query->execute([$orderId]);
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: please make sure the php redis extension is installed and enabled.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.