[SOLVED] Multiple insert query, how to ignore duplicate messages.

[Mark1inLA]

Hello all,

 

I added a composite key to a table that's lacking a primary key. Now i have an automated PHP script that utilizes the multi-insert query to populate this table on a daily basis. The issue I'm having is that when there's a duplicate value, i want to be able to add the ones that it can but ignore the ones that already exist.

 

Is there a way to get this result using PHP/MySQL?

[MattR]

Change INSERT INTO to REPLACE INTO?? Unless I'm misunderstanding what you mean.

[Mark1inLA]

Change INSERT INTO to REPLACE INTO?? Unless I'm misunderstanding what you mean.

 

Thanks for your response. In my situation, this worked. But in the MySQL certification study guide, i found some more info on the subject:

 

If you don't indicate explicitly how to handle a duplicate, MySQL aborts the statement with an error and discards the new record. This is the default behavior. (For multiple-record INSERT statements, treatment of records inserted before a record that causes a duplicate-key violation is dependent on the storage engine. For MyISAM, the records are inserted. For InnoDB, the entire statement fails and no records are inserted.)

 

You can tell MySQL to ignore the new record without producing an error. To do this, modify the statement so that it begins with INSERT IGNORE rather than with INSERT. If the record does not duplicate a unique key value, MySQL inserts it as usual. If the record does contain a duplicate key, MySQL ignores it. Client programs that terminate on statement errors will abort with INSERT but not with INSERT IGNORE.