"INSERT INTO .. ON DUPLICATE KEY UPDATE" Insert only new records, not replace them?

Question

I have a table, say table1, which has 3 columns: id, number and name. id is auto_incremented.

I want to get a sql statement that inserts records into a table, but if the row already exists then ignore it.

However

Every time I run:

INSERT INTO table1( number, name) 
VALUES(num, name) 
ON DUPLICATE KEY 
UPDATE number = VALUES(number), 
       name = VALUES(name)

It seems to ignore rows with matching numbers and names and add records to the end of the table no matter what.

Is there anything I can do to prevent this? I have a feeling it has something to do with the auto_incrementing primary key? thank

+1

mysql sql-server insert duplicates

enthalpy Jan 27. 15 at 22:03

source to share

1 answer

BK435 · Accepted Answer · 2015-01-27T22:15:25+0000

Create a unique composite index by number and name.

Alter table table1 Add unique index idx_unq(`number`,`name`);

Then run Insert Ignore INTO table1(number,name) VALUES(num, name);

This should prevent the duplicate from being inserted into the table.

Useful link to unique indexes

"INSERT INTO .. ​​ON DUPLICATE KEY UPDATE" Insert only new records, not replace them?