INSERT query with if exist statement

Question

I have two tables:

purchase_mis(id, user_id, total_purchased, date)
daily_purchase(id, user_id, product_id, paid_amount, purchase_date)

I have a CRON file that runs every night, it counts the daily purchase from the "daily_purchase" table and runs insert into "purchase_mis".

For example:

SELECT
    COUNT(*) AS purchase_count,
    purchase_date
FROM daily_purchase
GROUP BY user_id;

This returns the purchase_count for every user and then it will be inserted to the "purchase_mis" table.

INSERT INTO
    purchase_mis(user_id, total_purchased, date)
VALUES
    ('2', 'purchase_count', 'purchase_date');

But before inserting, it needs to check if the purchased information of user_id = 2 for some date "purchase_date" has already been inserted so it should not be inserted again.

I want something like the below query:

INSERT INTO
    purchase_mis(user_id, total_purchased, date)
VALUES
    ('2', 'purchase_count', 'purchase_date')
WHERE date NOT EXISTS (SELECT date FROM purchase_mis WHERE user_id = '2');

This is looks like a dup of http://stackoverflow.com/questions/913841/mysql-conditional-insert — Todd, Dec 06 '12 at 09:39

user7282 · Answer 1 · 2012-12-06T10:08:26.260

0

Use this

INSERT IGNORE INTO
    purchase_mis(user_id, total_purchased, date)
VALUES
    ('2', 'purchase_count', 'purchase_date');

edited Dec 06 '12 at 10:08

answered Dec 06 '12 at 09:38

user7282

5,106
9
41
72

Why should OP try this? Can you discuss why and maybe add more detail as to why this is the answer? – Jonas G. Drange Dec 06 '12 at 09:56

Sirko · Answer 2 · 2012-12-06T10:08:11.633

You could use an INSERT IGNORE statement. To do so, you have to have a unique key over user_id and date. Then the following query, will insert only those values, for which the key is not present (meaning users which had no entry for that date) and issue a warning for others, which you could ignore here.

INSERT IGNORE INTO
    purchase_mis(user_id, total_purchased, date)
( SELECT
    user_id,
    COUNT(*),
    purchase_date
FROM daily_purchase
GROUP BY user_id );

score 0 · Answer 3 · answered Dec 06 '12 at 09:46

Try something like this.

INSERT INTO purchase_mis(user_id, total_purchased, date)
SELECT user_id, purchase_date, count(*)
FROM daily_purchase a
LEFT OUTER JOIN purchase_mis b
ON a.user_id = b.user_id
AND a.purchase_date = b.date
WHERE b.user_id IS NULL
GROUP BY user_id, purchase_date

INSERT query with if exist statement

3 Answers3