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

Don't use character literals for numbers.`'2'` is a character, not a number. If `user_id` is defined as e.g. an integer, you should be using `2` - no single quotes around the value. — , Dec 06 '12 at 19:49

score 3 · Answer 1 · edited May 23 '17 at 12:29

3

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.

edited May 23 '17 at 12:29

Community

1
1

answered Dec 06 '12 at 06:20

cegfault

6,442
3
27
49

1

I think triggers are the appropriate technique – Eric Fossum Dec 06 '12 at 06:20
I cant use TRIGGER, because database structure is not that good. – Xi Kam Dec 06 '12 at 06:53
@XiKam: what about procedures? – cegfault Dec 06 '12 at 19:39

score 0 · Answer 2 · answered Dec 06 '12 at 19:56

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

MySQL database related

2 Answers2