Mysql - On Duplicate Key Update not working for 2 keys

Question

Hello I have a table with two unique keys profile_id and date. I don't know where is the problem but my query is not working.

Table:

CREATE TABLE `profile_views` 
    (\n  `id` int(11) NOT NULL AUTO_INCREMENT,
    \n  `profile_id` varchar(45) DEFAULT NULL,
    \n  `counter` varchar(45) DEFAULT NULL,
    \n  `date` date DEFAULT NULL,
    \n  PRIMARY KEY (`id`),
    \n  UNIQUE KEY `date_UNIQUE` (`date`),
    \n  UNIQUE KEY `profile_id_UNIQUE` (`profile_id`)\n
    ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=latin1'

Data Right Now:

# id , profile_id, counter, date
  113,      2    ,   36   , 2015-08-27

I issue this command:

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-28') 
ON DUPLICATE KEY UPDATE counter = counter+1;

And

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-27') 
ON DUPLICATE KEY UPDATE counter = counter+1;

In this query i just changed the date so it should insert new row.

My Desired Results:

If i change date still it changing the same profile id counter. I want to store everyday profile views for each profile id. So if the date and profile id is same its increment the counter otherwise insert new row.

Any help? Thanks.

show the complete output of running`show create table profile_views`. Also, show a row or 2 of sample data, before and after, and desired results. So that's 4 things — Drew, Aug 27 '15 at 21:33
ok, so you say result right now. Let's call that Data right now. Then you issue what, and it returns what, and you would rather have what. Those are 3 things — Drew, Aug 27 '15 at 22:02
there, I blocked off the question with an edit to it. Fill in those 3 blanks. thx — Drew, Aug 27 '15 at 22:05
works fine for me - inserts where no record exists and then proceeds to update if it does! — Professor Abronsius, Aug 27 '15 at 22:08

Drew · Accepted Answer · 2015-08-27T22:45:44.653

0

Schema:

CREATE TABLE `profile_views` 
(  
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `profile_id` varchar(45) DEFAULT NULL,
   `counter` varchar(45) DEFAULT NULL,
   `date` date DEFAULT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `date_UNIQUE` (`date`),
   UNIQUE KEY `profile_id_UNIQUE` (`profile_id`)
) ENGINE=InnoDB auto_increment=150;

insert profile_views (id,profile_id,counter,date) values (113,2,36,'2015-08-27');

... ...

select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 36      | 2015-08-27 |
+-----+------------+---------+------------+

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-28') 
ON DUPLICATE KEY UPDATE counter = counter+1;
-- 2 row(s) affected
select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 37      | 2015-08-27 |
+-----+------------+---------+------------+

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-27') 
ON DUPLICATE KEY UPDATE counter = counter+1;
-- 2 row(s) affected
select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 38      | 2015-08-27 |
+-----+------------+---------+------------+

It looks good to me. Each insert on duplicate update has a unique key clash, allowing the update to happen. What clashes? Well the unique key on profile_id does.

What am I missing?

If you lay things out step by step, people can visualize it better :>

Edit: (OP changed his mind)

CREATE TABLE `profile_views` 
(  
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `profile_id` varchar(45) DEFAULT NULL,
    `counter` varchar(45) DEFAULT NULL,
    `date` date DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `combo_thingie1` (profile_id,`date`)  -- unique composite
) ENGINE=InnoDB auto_increment=150;

insert profile_views (id,profile_id,counter,date) values (113,2,36,'2015-08-27');

... ...

select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 36      | 2015-08-27 |
+-----+------------+---------+------------+

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-28') 
ON DUPLICATE KEY UPDATE counter = counter+1;

select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 36      | 2015-08-27 |
| 150 | 2          | 1       | 2015-08-28 |
+-----+------------+---------+------------+

INSERT INTO profile_views (profile_id, counter, date) 
VALUES (2, 1, '2015-08-27') 
ON DUPLICATE KEY UPDATE counter = counter+1;

select * from profile_views;
+-----+------------+---------+------------+
| id  | profile_id | counter | date       |
+-----+------------+---------+------------+
| 113 | 2          | 37      | 2015-08-27 |
| 150 | 2          | 1       | 2015-08-28 |
+-----+------------+---------+------------+

edited Aug 27 '15 at 22:45

answered Aug 27 '15 at 22:22

Drew

24,851
10
43
78

In the first query you insert date 2015-08-27 with id 2. In the second query you insert date 2015-08-28 with same profile id 2. But it should add a new row because date is different. – Yas Aug 27 '15 at 22:25
I insert with **profile_id** = 2 if that is what you mean, on ALL 3 inserts – Drew Aug 27 '15 at 22:26
and profile_id has a unique key as per the schema you gave us all – Drew Aug 27 '15 at 22:27
it doesn't matter that the date changed to a different one (as we all know you have a unique key on that too). the clash was on a dupe **profile_id** – Drew Aug 27 '15 at 22:28
Sorry for my poor explanation. In the first query you insert date 2015-08-27 with profile_id = 2. In the second query you insert date 2015-08-28 with profile_id = 2. Now the date is different so it should insert a new row rather then updating the same one because one of the unique keys value is different. – Yas Aug 27 '15 at 22:30
now if you were to say that your expected results were 2 rows at the end, that is not going to happen, cuz of my prev comment – Drew Aug 27 '15 at 22:30
No, sir. That is not how it works. If any **key** clashes, the update occurs. Because 2 rows with profile_id = 2 violates your schema. – Drew Aug 27 '15 at 22:31
so re-think what you want to do – Drew Aug 27 '15 at 22:31
I want to store each profile views with date so later i can view how many profile views daily,monthly,yearly. – Yas Aug 27 '15 at 22:32
then have a unique composite index. I will show you that – Drew Aug 27 '15 at 22:32
if you want many rows in there with profile_id = 2, then say so. I haven't heard that yet – Drew Aug 27 '15 at 22:33
It's my wrong understanding about unique keys i through if there is two unique keys and if its match it will update otherwise insert. Any other query suggestion to do this please? – Yas Aug 27 '15 at 22:34
there is but you need to convey it. what combination of rows do you want? – Drew Aug 27 '15 at 22:35
it would go something like this, maybe: "I want unique combinations of profile_id and date. That is, only 1 row can be of that combo" ... something like that. – Drew Aug 27 '15 at 22:36
or I only want 1 row for a given date ever, or I want only 1 row for a profile_id ever – Drew Aug 27 '15 at 22:36
I don't want to insert multiple row for same profile id so what i want to do is just to store profile view for each profile id with date so later i can see in my front end how many profile views daily, monthly and yearly. – Yas Aug 27 '15 at 22:36
so you only want one row for a profile_id and that is it? That is all you want – Drew Aug 27 '15 at 22:38
From your comment i can say i am looking for combination of profile_id and date in a one row. – Yas Aug 27 '15 at 22:38
Profile_id = 2 date = 27 Aug, counter = 100, Profile_id = 2 date = 28 Aug, counter = 80. – Yas Aug 27 '15 at 22:40
look at the Edit at bottom of Answer. Is that what you want? – Drew Aug 27 '15 at 22:46

score 0 · Answer 2 · answered Aug 27 '15 at 22:42

I came up with this mad structure - it inserts new records for new dates and then updates on successive insert statements - thus incrementing the counter.

   CREATE TABLE `profile_views` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `profile_id` VARCHAR(45) NOT NULL,
        `counter` VARCHAR(45) NOT NULL,
        `date` DATE NOT NULL,
        PRIMARY KEY (`id`, `profile_id`, `date`),
        UNIQUE INDEX `profile_id_date` (`profile_id`, `date`),
        UNIQUE INDEX `id_profile_id_date` (`id`, `profile_id`, `date`)
    )
    COLLATE='latin1_swedish_ci'
    ENGINE=InnoDB
    AUTO_INCREMENT=267;

Hi thank you for your help i just found my solution :) – Yas Aug 27 '15 at 22:52 — Yas, Aug 27 '15 at 22:52

Mysql - On Duplicate Key Update not working for 2 keys

2 Answers2

Schema:

Edit: (OP changed his mind)