CODEWITHSUNDEEP
mysqlinsertduplicates
Shar678
Shar678

Reputation: 35

MySQL insert but update/ignore duplicate

This is my table with some sample data

C ID | D ID    | Details |
--------------------------
 a   | b_data1 | d1      |
 a   | b_data2 | d2      |
 b   | b_data1 | d1      |
 b   | b_data2 | d2      |
 c   | b_data1 | d1      |
 c   | b_data2 | d2      |

When I run this query##

INSERT IGNORE INTO table_name (C_ID, D_ID, Details) VALUES ('C', 'b_data3','d3') ('C', 'b_data2','d2')

It inserts both those rows when it's supposed to ignore the second value pair ('C', 'b_data2','d2')

No indexes /primary key are defined.

I want to make sure there is no duplicate row, means data in three columns combined should make the row unique. I can't make it unique .As I have illustrated here, a can keep same content as b but a should not have a duplicate.

Upvotes: 0

Views: 2834

Answers (3)

Miloš Rašić
Miloš Rašić

Reputation: 2279

MySQL enforces unique constraints with UNIQUE indexes. If you wish to add the index without recreating the table, you would do:

CREATE UNIQUE INDEX index_name ON table (C_ID, D_ID, Details)

In addition to INSERT IGNORE and the already mentioned REPLACE, you can also use INSERT...ON DUPLICATE KEY UPDATE for more control over what happens when insert runs into a duplicate unique.

Upvotes: 1

Aleš Kotnik
Aleš Kotnik

Reputation: 2724

You should define composed uniqe index as @lanzz suggested. If you want new records to replace the old ones in the case of index clash, use replace statement.

Upvotes: 0

lanzz
lanzz

Reputation: 43168

INSERT IGNORE means "ignore rows that break unique constraints, instead of failing the query". You need to define UNIQUE columns for it to work as you expect it to — e.g. CREATE TABLE tablename (col1 type, col2 type, col3 type, UNIQUE (col1, col2, col3))

Upvotes: 2

Related Questions