MySQL update a record on duplicate key update (merge)

bulkMySQLupsert

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

Best Answer

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