Insert into table but avoid duplicates for multiple columns

Question

I would like to insert into table if table does not already contain those values.

This is my table

ID | Col1 | Col2 | Col3 | Col4

My table contains the following data:

0 | "a1" | "a2" | "a3" | "a4" |

1 | "b1" | "b2" | "b3" | "b4" |

2 | "c1" | "c2" | "c3" | "c4" |

I would like to not allow insert of the following input:

3 | "b1" | "b2" | "b3" | "d4" |

But allow the following:

3 | "a1" | "b2" | "c3" | "d4" |

I have tried the following

INSERT INTO my_table (Col1, Col2, Col3, Col4) SELECT * FROM (SELECT :val1, :val2, :val3, :val4) as tmp WHERE NOT EXISTS (SELECT 1 FROM my_table WHERE Col1=:val1 AND Col2=:val2 AND Col3=:Val3)

This works fine unless :val1==:val2.

Is there a better way to insert and avoid duplicates?

I'm not clear what you mean by a duplicate here. Is it a duplicate if the values are in any order in col1,2,3,4? for example is it a duplicate if you already have 5,6,7,8 and you present 8,5,7,6? — P.Salmon, Oct 11 '19 at 14:08
That would not be a duplicate. A duplicate would be if and only if all columns have the same value. — Nick Arnie, Oct 14 '19 at 06:08

score 1 · Answer 1 · answered Oct 11 '19 at 13:59

1

you can create unique constraint/primary key by combining (Col1, Col2, Col3) to insert unique records.

answered Oct 11 '19 at 13:59

SJN

377
2
8
18

This does sound like a good solution but I would like to avoid creating another column. – Nick Arnie Oct 14 '19 at 06:13
1

You don't need to create a new column, you need to just create an unique constraint on these three columns. – SJN Oct 14 '19 at 06:40
@SJN please add example as well. – user6159419 Jan 28 '21 at 15:49

score 0 · Answer 2 · answered Oct 11 '19 at 14:00

0

You may use

INSERT IGNORE

so it will ignore records that already exist in the table.

REPLACE https://dev.mysql.com/doc/refman/5.5/en/replace.html

to replace the existing record

answered Oct 11 '19 at 14:00

rai

449
3
10

1

Insert ignore only ignores errors. That is not what i am looking for. – Nick Arnie Oct 14 '19 at 06:13

Insert into table but avoid duplicates for multiple columns

2 Answers2