Sample Header Ad - 728x90

MySQL update a record on duplicate key update (merge)

0 votes
2 answers
789 views
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)?
Asked by Pioz (101 rep)
Mar 26, 2021, 10:42 AM
Last activity: Feb 21, 2024, 03:02 PM