MySQL update a record on duplicate key update (merge)

Question

I have a table products with the follow schema:

CREATE TABLE `products` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `article_id` bigint unsigned NOT NULL,
  `price_cents` int unsigned NOT NULL,
  `quantity` smallint NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_products_unique` (`user_id`,`article_id`,`price_cents`),
  KEY `fk_products_article` (`article_id`),
  CONSTRAINT `fk_products_article` FOREIGN KEY (`article_id`) REFERENCES `articles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_products_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Now I can do INSERT with this query:

INSERT INTO `products` (`user_id`,`article_id`,`price_cents`,`quantity`) 
VALUES (1,1,200,1) 
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

So now I have 1 product (ID 1) with quantity 1 and price 200. Now I insert 2 more products with:

INSERT INTO `products` (`user_id`,`article_id`,`price_cents`,`quantity`) 
VALUES (1,1,200,1),(1,1,199,1) 
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

Now I have 2 products, one (ID 1) with quantity 2 and price 200 and the other (ID 2) with quantity 1 and price 199.

Good.

The problem comes now: I want to update the product with price 199 and set a new price to 200. What I do is:

INSERT INTO `products` (`id`,`user_id`,`article_id`,`price_cents`,`quantity`) 
VALUES (2,1,1,200) 
ON DUPLICATE KEY UPDATE `price_cents`=VALUES(`price_cents`),`quantity`=quantity+VALUES(`quantity`)

and what I would like is a single product with id 1, price 200, and quantity 3, but I get Number:0x426, Message: "Duplicate entry '1-1-200' for key 'products.idx_products_unique' because MySQL does not delete the product with ID 2.

Is there a way to achieve this in MySQL (keep in mind that I want to perform these operations in bulk)?

Answer 1

Which do you want? Do you want to tally the number of each item, and keep the latest price? Or do you want to tally each item-price combo. That is, should 199 and 200 take 2 rows or just one?

UNIQUE(user_id, article_id)

says one row and can keep only the latest price as you are doing with

`price_cents`=VALUES(`price_cents`)

The 3-column UNIQUE key is in conflict with that.

IODKU decides which row(s) to modify based on all unique keys (including the PK) for which you have specified columns.

Meanwhile, consider getting rid of id and promoting the UNIQUE key to be the PRIMARY KEY. (A PK is a unique key is an index.)

Answer 2

you can use UPDATE like this:

UPDATE `products`
SET `price` = 200
WHERE ID = 2;

or REPLACE like this:

REPLACE into products (id, price) values(2, 200);