MySQL - Insert a record only if it doesn't exist

By xngo on February 21, 2019

-- Create test data.
 DROP TABLE person;
 CREATE TABLE person
 (
    id INT NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    PRIMARY KEY (id)
 );
 
 INSERT INTO person(firstname, lastname) VALUES('John', 'Smith');
 INSERT INTO person(firstname, lastname) VALUES('Lisa', 'Simpson');
 
-- Here is the single query where it checks whether the record is a duplicate 
--  before inserting it into the database.
--  If (SELECT * FROM person WHERE firstname='John' and lastname='Smith') exists, then add
--  "'John', 'Smith'" in the person table.
--  Note: dual is a dummy table. It is used to make the query syntactically correct.
 INSERT INTO person(firstname, lastname) SELECT 'John', 'Smith' FROM dual WHERE NOT EXISTS 
   (SELECT * FROM person WHERE firstname='John' AND lastname='Smith');
 
 INSERT INTO person(firstname, lastname) SELECT 'Xuan', 'Ngo' FROM dual WHERE NOT EXISTS 
  (SELECT * FROM person WHERE firstname='Xuan' AND lastname='Ngo');
 
-- Display results.
 SELECT * FROM person;

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.