MySQL insert but update/ignore duplicate

Question

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.

lanzz · Accepted Answer · 2012-05-27 16:26:51Z

2

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

answered May 27, 2012 at 16:26

lanzz

43.4k12 gold badges93 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shar678 Over a year ago

I cant make it unique as this is a collection page, E.g should be able to store a > b_data1 > d1 and a > b_data2 > d2 and b b > b_data1 > d1 and b > b_data2 > d2 table about i illustrated. a can have same content of collection b but cant have same in a

lanzz Over a year ago

If you define UNIQUE (col1, col2, col3), this means you cannot have the same values in ALL of these columns. I.e., you can have the same value in col1 as long as either col2 or col3 is different. Or you can have the same value in col2 and col3 as long as col1 is different. It is a compound index on all three columns, does not mean that each of them needs to be unique.

Shar678 Over a year ago

Thanks alot for ur helps .. I'm clear now on its usage .. i cant create unique/primary key in this table as there is no unique single colum. its unique when all 3 combined. i had to query and do !in_array($data, $query_array) to over come this

Miloš Rašić · Accepted Answer · 2012-05-27 16:38:50Z

1

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.

answered May 27, 2012 at 16:38

Miloš Rašić

2,2795 gold badges24 silver badges44 bronze badges

3 Comments

Shar678 Over a year ago

but i cant make it unique. i have illustrated there . a can keep same content as b but a should not have a duplicate

Aleš Kotnik Over a year ago

I'm afraid, MySql requires primary key or uniqe index to keep duplicates away.

Shar678 Over a year ago

Thanks alot for ur helps .. I'm clear now on its usage .. i cant create unique/primary key . i had to query and do !in_array($data, $query_array) to over come this

Aleš Kotnik · Accepted Answer · 2012-05-27 16:33:38Z

0

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.

answered May 27, 2012 at 16:33

Aleš Kotnik

2,75420 silver badges18 bronze badges

1 Comment

Shar678 Over a year ago

but i cant make it unique. i have illustrated there . a can keep same content as b but a should not have a duplicate

Collectives™ on Stack Overflow

MySQL insert but update/ignore duplicate

3 Answers 3

3 Comments

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

1 Comment

Related