CODEWITHSUNDEEP
mysqlsqlduplicatessql-insert
Csaba
Csaba

Reputation: 2086

MySql Insert multiple and Ignore duplicate records

I have this MySQL INSERT query that adds a Product to multiple categories:

INSERT INTO _categories_products (product_id, category_id) VALUES (1, 14), (1, 8), (1, 1), (1, 22);

This works great, however, if I add the same product to another subcategory from the same parent, it will create duplicate records for the parent categories:

INSERT INTO _categories_products (product_id, category_id) VALUES (1, 14), (1, 8), (1, 1), (1, 23);

enter image description here

Question: What would be the appropriate MySQL query that Ignores the insertion of duplicate records? In other words, the second query should INSERT only one record: 1, 23.

I tried also INSERT IGNORE INTO but nothing changed.

Thank You!

Upvotes: 0

Views: 1319

Answers (1)

GMB
GMB

Reputation: 222402

To start with, you want to create a unique constraint on categories/product tuples to avoid duplicates:

alter table _categories_products  
    add constraint _categories_products _bk 
    unique (product_id, category_id);

From that point on, an attempt to insert duplicates in the table would, by default, raise an error. You can trap and manage that error with MySQL on duplicate key syntax:

insert into _categories_products (product_id, category_id) 
values (1, 14), (1, 8), (1, 1), (1, 23)
on duplicate key update product_id = values(product_id)

In the case of duplicate, the above query performs a dumb update on product_id, which actually turns the insert to a no-op.

Upvotes: 5

Related Questions