I'm trying to get a sql select insede an update, but always return null.

UPDATE ps_stock_available sa
SET sa.reserved_quantity = (
    SELECT IFNULL(SUM(od.product_quantity - od.product_quantity_refunded),0) 
    FROM ps_orders o
    INNER JOIN ps_order_detail od ON od.id_order = o.id_order
    INNER JOIN ps_order_state os ON os.id_order_state = o.current_state
    WHERE o.id_shop = 1 AND
    os.shipped != 1 AND (
        o.valid = 1 OR (
            os.id_order_state NOT IN ('6,28,59') AND
            os.id_order_state NOT IN ('8')
        )
    ) AND sa.id_product = od.product_id AND
    sa.id_product_attribute = od.product_attribute_id
    GROUP BY od.product_id, od.product_attribute_id
)
WHERE sa.id_shop = 1 AND sa.id_product = 3374;

If the sum is null, the subquery has to return 0. I get this query update from prestashop class (StockManager.php, method: updateReservedProductQuantity).

This is the original code:

$updateReservedQuantityQuery = '
            UPDATE {table_prefix}stock_available sa
            SET sa.reserved_quantity = (
                SELECT SUM(od.product_quantity - od.product_quantity_refunded)
                FROM {table_prefix}orders o
                INNER JOIN {table_prefix}order_detail od ON od.id_order = o.id_order
                INNER JOIN {table_prefix}order_state os ON os.id_order_state = o.current_state
                WHERE o.id_shop = :shop_id AND
                os.shipped != 1 AND (
                    o.valid = 1 OR (
                        os.id_order_state != :error_state AND
                        os.id_order_state != :cancellation_state
                    )
                ) AND sa.id_product = od.product_id AND
                sa.id_product_attribute = od.product_attribute_id
                GROUP BY od.product_id, od.product_attribute_id
            )
            WHERE sa.id_shop = :shop_id
        ';

        $strParams = array(
            '{table_prefix}' => _DB_PREFIX_,
            ':shop_id' => (int) $shopId,
            ':error_state' => (int) $errorState,
            ':cancellation_state' => (int) $cancellationState,
        );

        if ($idProduct) {
            $updateReservedQuantityQuery .= ' AND sa.id_product = :product_id';
            $strParams[':product_id'] = (int) $idProduct;
        }

        if ($idOrder) {
            $updateReservedQuantityQuery .= ' AND sa.id_product IN (SELECT product_id FROM {table_prefix}order_detail WHERE id_order = :order_id)';
            $strParams[':order_id'] = (int) $idOrder;
        }

        $updateReservedQuantityQuery = strtr($updateReservedQuantityQuery, $strParams); 

Why always return null ?

CodePudding user response：

You need to put the ifnull check on the outer query, not the inner, which can return no rows.

You can use coalesce here, such as:

UPDATE ps_stock_available sa
SET sa.reserved_quantity = coalesce((
    SELECT SUM(od.product_quantity - od.product_quantity_refunded)
    FROM ps_orders o
    INNER JOIN ps_order_detail od ON od.id_order = o.id_order
    INNER JOIN ps_order_state os ON os.id_order_state = o.current_state
    WHERE o.id_shop = 1 AND
    os.shipped != 1 AND (
        o.valid = 1 OR (
            os.id_order_state NOT IN ('6,28,59') AND
            os.id_order_state NOT IN ('8')
        )
    ) AND sa.id_product = od.product_id AND
    sa.id_product_attribute = od.product_attribute_id
    GROUP BY od.product_id, od.product_attribute_id
), 0)
WHERE sa.id_shop = 1 AND sa.id_product = 3374;