MySQL database related

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');

Answer 1

insert into purchase_mis
   (user_id, total_purchased, date)
select * 
from (
   select 2 as id, 
          100 as user_id, 
          str_to_date('2012-12-04', '%Y-%m-%d') as purchase_date
) t 
where not exists (SELECT 1 
                  FROM purchase_mis pm
                  WHERE pm.user_id = t.id 
                  and pm.date = t.purchase_date);

Answer 2

Create a key on the date column, and then use INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE syntax. See this for more: "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"

Also, you can probably use triggers or procedures instead of a cron job; might make life a bit easier.