CODEWITHSUNDEEP
mysql
galdikas
galdikas

Reputation: 1669

Ignore insert if value is null

So I have query like so:

INSERT INTO price_hist 
            ( 
                        date, 
                        product_id, 
                        price_min, 
                        price_max, 
                        price_avg,
                        merchants 
            ) 
            VALUES 
            ( 
                        '2015-07-10', 
                        100388, 
                        ( 
                               SELECT min(price) 
                               FROM   prices 
                               WHERE  product_id = 100388 
                               AND    active = 1 ), 
                        ( 
                               SELECT max(price) 
                               FROM   prices 
                               WHERE  product_id = 100388 
                               AND    active = 1 ), 
                        ( 
                               SELECT avg(price) 
                               FROM   prices
                               WHERE  product_id = 100388 
                               AND    active = 1 ), 
                        ( 
                               SELECT count(price) 
                               FROM   prices
                               WHERE  product_id = 100388 
                               AND    active = 1 ) 
            )

price_max, price_min and price_avg cannot be null, but not all products have prices, so naturally subqueries for those products returns NULL. If I use INSERT IGNORE, the query does execute, but instead of skipping the insert it Insert 0 values to all these fields. Any way, I can modify it, so it skips the insert all together, should any fields be null?

product_history table already has the fields set up, so they can't be NULL.

Upvotes: 0

Views: 2130

Answers (3)

Vatev
Vatev

Reputation: 7590

Just use an INSERT ... SELECT instead:

INSERT INTO price_hist 
( 
    date, 
    product_id, 
    price_min, 
    price_max, 
    price_avg,
    merchants 
) 
SELECT 
    '2015-07-10', 
    100388, 
    min(price),
    max(price),
    avg(price), 
    count(price) 
FROM prices
WHERE product_id = 100388
AND active = 1
HAVING count(price)

Upvotes: 2

Markus
Markus

Reputation: 693

INSERT INTO price_hist 
            ( 
                        date, 
                        product_id, 
                        price_min, 
                        price_max, 
                        price_avg,
                        merchants 
            ) 
            VALUES 
            ( 
                        '2015-07-10', 
                        100388, 
                        ( 
                               SELECT ifnull(min(price),0)
                               FROM   prices 
                               WHERE  product_id = 100388 
                               AND    active = 1 ), 
                        ( 
                               SELECT max(price) 
                               FROM   prices 
                               WHERE  product_id = 100388 
                               AND    active = 1 ), 
                        ( 
                               SELECT ifnull(avg(price),0)
                               FROM   prices
                               WHERE  product_id = 100388 
                               AND    active = 1 ), 
                        ( 
                               SELECT count(price)
                               FROM   prices
                               WHERE  product_id = 100388 
                               AND    active = 1 ) 
            )

Upvotes: 0

Agung Santoso
Agung Santoso

Reputation: 68

Use IFNULL()

Try this

INSERT INTO price_hist 
        ( 
                    date, 
                    product_id, 
                    price_min, 
                    price_max, 
                    price_avg,
                    merchants 
        ) 
        VALUES 
        ( 
                    '2015-07-10', 
                    100388, 
                    ( 
                           IFNULL(SELECT min(price) 
                           FROM   prices 
                           WHERE  product_id = 100388 
                           AND    active = 1 ), 0), 
                    ( 
                           IFNULL(SELECT max(price) 
                           FROM   prices 
                           WHERE  product_id = 100388 
                           AND    active = 1 ), 0), 
                    ( 
                           IFNULL(SELECT avg(price) 
                           FROM   prices
                           WHERE  product_id = 100388 
                           AND    active = 1 ), 0), 
                    ( 
                           IFNULL(SELECT count(price) 
                           FROM   prices
                           WHERE  product_id = 100388 
                           AND    active = 1 ), 0) 
        )

Upvotes: 0

Related Questions