I want to update the purchases table quantity value from order_item if the order is canceled in php

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;
+----+----------+
| id | quantity |
+----+----------+
|  1 |       93 |
+----+----------+
1 row in set (0.00 sec)

mysql> select order_id,purchase_id,quantity from order_item;
+----------+-------------+----------+
| order_id | purchase_id | quantity |
+----------+-------------+----------+
|        1 |           1 |        2 |
|        2 |           1 |        1 |
|        3 |           1 |        2 |
|        4 |           1 |        2 |
+----------+-------------+----------+
4 rows in set (0.00 sec)

expected answer should add the quantities from order_item which match the order_item.purchase_id = purchases.id

Answer

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 with SUM()? 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]);