Updating a table with a composite key not working correctly

Question

I have the following table:

As you can see, date and bed form a composite primary key.

I am trying to run this query:

INSERT INTO days (date, operating_time, bed) VALUES ('2016-11-07', 6.55, 1) ON duplicate key update operating_time=VALUES(operating_time);

The problem is that it seems to update based only on the date column. So for example, running the above query for a bed value of 1, and then re-running it but with a bed value of 2 will actually update the original record (the one where bed = 1)

How can I make the ON DUPLICATE KEY statement check both the date and bed columns?

Edit

Here is the create statement for the table:

CREATE TABLE `days` (
  `date` date NOT NULL,
  `operating_time` float DEFAULT '0',
  `bed` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`bed`),
  UNIQUE KEY `date_UNIQUE` (`date`),
  KEY `bed_idx` (`bed`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

@barudo I just tried it again on a completely blank table with the exact structure as above. It did not work. — Matthew Goulart, Nov 24 '16 at 22:29
its because of the UNIQUE key on date... so mysql cannot add another row (they have the same date). If you'll try to have the same bed and date (but different operating time), it will update the operating time. — barudo, Nov 24 '16 at 22:53
yes, mine failed as soon as I added that (actually it would not let me add the UNIQUE index to the table with data in it) — Ňɏssa Pøngjǣrdenlarp, Nov 24 '16 at 22:54
I changed the unique key to both the date and bed columns and it worked.... I didn't know a composite key had to be UNIQUE and not primary... Curious... — Matthew Goulart, Nov 25 '16 at 12:12

score 2 · Answer 1 · answered Nov 24 '16 at 22:58

The table days has a UNIQUE index on date so if you do an INSERT with a same date but different bed, it won't be inserted because of the UNIQUE (else it fails the unique index).

If you try inserting with the same bed and date (but different operating_time), it will make an UPDATE.

score 0 · Accepted Answer · answered Dec 01 '16 at 14:09

After posting the create statement for the table, it because evident that the UNIQUE key was composed of only the date column. After adding the bed column, it worked as expected. I will need to read up on the different types of keys and why specifically the UNIQUE key must contain both columns used in the composite key.

Updating a table with a composite key not working correctly

2 Answers2