CODEWITHSUNDEEP
phpmysqldatabasenullsubquery
Bea Lovelace
Bea Lovelace

Reputation: 55

UPDATE with subquery returning EMPTY/NULL MYSQL

I have this database:

I want to update the column "total_sales" using this query

UPDATE products p SET total_sales = (SELECT SUM(price) AS totalPrice
FROM `products_sales` WHERE id_product = p.id GROUP BY id_product)

But if one product doesn't have any sale I get this error:

#1048 - Column 'total_sales' cannot be null

How can I avoid this error when a product has no sales?

Maybe using a fallback value?

Upvotes: 2

Views: 337

Answers (1)

Ersoy
Ersoy

Reputation: 9586

As a fallback you may use zero with COALESCE

UPDATE
    products AS p
        INNER JOIN (
        SELECT id_product, COALESCE(SUM(price), 0) AS totalPrice
        FROM `products_sales`
        GROUP BY id_product
    ) AS pSub ON p.id = pSub.id_product
SET p.total_sales = pSub.totalPrice;

Upvotes: 2

Related Questions