0

I have a table constructed by the followinng:

CREATE TABLE IF NOT EXISTS test_table (
ID int(11) NOT NULL AUTO_INCREMENT,
ProfileID int(11) NOT NULL,
ForeignID int(11) NOT NULL,
PRIMARY KEY (ProfileID,ForeignID) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I want to do something a little peculiar though, say there are 4 records in the database:

RecA, RecB, RecC, RecD

I would like to run the following query and have the insert behavior stop when a duplicate key was encountered:

INSERT IGNORE INTO test_table (ProfileID, ForeignID) VALUES(RecE, RecF, RecA, RecB, RecG);

So the query would only insert RecE and RecF, is there a way to do this in MySQL, perhaps using ON DUPLICATE KEY? Ideally the execution would just be terminated once a duplicate has been found, I am not too familiar with SQL syntax though.

Where RecG was explicitly not inserted.

Danny Birch
  • 603
  • 4
  • 16
  • Add a constraint to the table? – Ed Heal Dec 30 '14 at 18:04
  • possible duplicate of ["INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) – xQbert Dec 30 '14 at 18:05
  • 1
    Sorry, I have made the question less vague in response to these comments^ I do not simply want to ignore duplicates – Danny Birch Dec 30 '14 at 18:07
  • What application code or client are you using to do these insertions? Many APIs would throw an error on key violation on which you may halt. – Michael Berkowski Dec 30 '14 at 18:13
  • I am using PHP to do the inserts, although I am trying to solve the problem iteratively like this rather than doing a SELECT on all the current records and do the same logic in PHP! Sorry, I messed up the primary key in the example – Danny Birch Dec 30 '14 at 18:14

0 Answers0