0

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

3
  • 1
    i find you logic strange. it seems you eant to know the quantity for every pürice, but hen you can't update id 2, so you shpould first think about what exactly do you need and thenevemtually rebuil your model – nbk Mar 26 at 11:50
  • I know all the new prices for every single product (CSV or excel file for example). I need to run a task to update all prices of my products (in bulk) without break the constraint of the uniqueness index. – Pioz Mar 26 at 11:56
  • 1
    use a trigger to update price ot remove the unique to user and article – nbk Mar 26 at 12:11
0

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);
3
  • But this is not a bulk update. – Pioz Mar 26 at 13:01
  • 1
    an update creates an error because there is already such a row, the concept is wrong and must be revised – nbk Mar 26 at 13:05
  • Remove this UNIQUE KEY `idx_products_unique` (`user_id`,`article_id`,`price_cents`), from your table model, because you are trying to insert duplicates in a UNIQUE KEY columns, which is impossible by default. – T0ny1234 Mar 26 at 13:11
0

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

2
  • I want to keep 2 rows if the price is different, but merge in one row and update the quantity if the price becomes the same. – Pioz Mar 26 at 16:58
  • @Pioz - How do you distinguish between keeping 2 prices versus "price becomes the same"? My Answer leads to either keeping all prices or keeping only the latest price. I don't understand how to define some combination of the two. – Rick James Mar 26 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.