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
Last activity: Feb 21, 2024, 03:02 PM