CODEWITHSUNDEEP
mysqlsqlunique-key
Vincent
Vincent

Reputation: 2169

MySQL on duplicate key update is not updating

I'm trying to use on duplicate key update but it's not affecting any rows.

My table create statement, where you can see that I've created a unique key on childid and date.

CREATE TABLE `history_childfees` (
    `childid` int(11) DEFAULT NULL,
    `date` date DEFAULT NULL,
    `amount` decimal(10,2) DEFAULT NULL,
    `feetypecode` varchar(45) DEFAULT NULL,
    UNIQUE KEY `key_childdate` (`childid`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

These are the two rows I have in the table.

enter image description here

The row I'm trying to update is the first row, by changing the amount for child 86615 on 2019-03-22.

insert into history_childfees (childid,date,amount,feetypecode)
values(86615,'2019-03-22',50,'DAY')
on duplicate key update childid = 86615, date = '2019-03-22';

I've also tried this syntax.

insert into history_childfees (childid,date,amount,feetypecode)
values (86615,'2019-03-22',50,'DAY')
on duplicate key update childid = values(childid), date = values(date);

Either way, it does not perform an insert and there's no error when I execute but it affects 0 rows. What am I missing here?

Upvotes: 0

Views: 64

Answers (1)

GMB
GMB

Reputation: 222702

Consider:

CREATE TABLE `history_childfees` (
    ...  
    UNIQUE KEY `key_childdate` (`childid`,`date`)
);

And:

insert into history_childfees
...
on duplicate key update childid = 86615, date = '2019-03-22'

The columns that you update on duplicate key are exactly those of the UNIQUE KEY that you are using to identify duplicates. By design, we already know that the values do match... As a consequence, the query leaves duplicate records unmodified.

If I followed you correctly, you probably want:

insert into history_childfees
...
on duplicate key update amount = 50

Upvotes: 1

Related Questions