CODEWITHSUNDEEP
phpmysqlsql
Anthony
Anthony

Reputation: 658

Ignore duplicates on insert with composite key

Is there a way to INSERT IGNORE INTO, with a composite key of certain columns?

INSERT IGNORE INTO `table1` WHERE `col_a` = 1 AND `col_b` = 2;

Where there is a composite key of col_a_col_b.

So basically it would ignore duplicates of certain row combinations?

Right now i've made a PHP function to ween out duplicates before inserting, but it's incredibly slow. I was wondering if SQL could do it alone?

Upvotes: 0

Views: 1223

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Is this what you are looking for?

INSERT IGNORE INTO table1(col_a, col_b) 
    select 1, 2;

This will insert the value pair (1, 2) every time you run it. If you only want the pair to exist once, then you need a unique constraint. The easiest way is with an index:

create unique index table1(col_a, col_b);

Then the above will work the first time, but fail for subsequent inserts. By the way, for this functionality, I prefer:

INSERT INTO table1(col_a, col_b) 
    select 1, 2
    on duplicate key update col_a = values(col_a);

insert ignore ignores all errors. This will only ignore errors caused by duplication -- nothing will happen and no errors will be returned.

Upvotes: 2

Related Questions