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. — user330315
– user330315, Commented Dec 6, 2012 at 19:49

Community · Accepted Answer · 2017-05-23 12:29:20Z

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, 2017 at 12:29

CommunityBot

11 silver badge

answered Dec 6, 2012 at 6:20

cegfault

6,6523 gold badges30 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eric Fossum Over a year ago

I think triggers are the appropriate technique

Xi Kam Over a year ago

I cant use TRIGGER, because database structure is not that good.

cegfault Over a year ago

@XiKam: what about procedures?

user330315 · Accepted Answer · 2012-12-06 19:56:32Z

0

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

answered Dec 6, 2012 at 19:56

user330315

Collectives™ on Stack Overflow

MySQL database related

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related