Conditional INSERT in SQL

Posted: 23rd October 2013 by Tim in SQL
Tags: , , , , , ,

Sometimes you want to run an INSERT statement in SQL only if some condition is met. There are a few methods available to do this, but not all of them are supported by all database systems. One method which is supported on all systems the use of a SELECT statement to return the row values, with the condition set in that SELECT statement:

INSERT INTO <table> (<col1>, <col2>)
SELECT <val1>, <val2>
WHERE <condition>

One practical example of this is inserting a row only if it does not already exist. This can be done like so:

Table definition:
CREATE TABLE person (person_id int PRIMARY KEY, name varchar(20));

Row Insert:
INSERT INTO person (person_id, name)
SELECT 1, 'Me'
WHERE NOT EXISTS (SELECT 1 FROM person WHERE person_id = 1);

Running the row insert query for the first time will result in the row being inserted. If run a second time, no row is inserted because a row with person_id = 1 already exists.

  1. Anurag says:

    This is excellent article, many thanks Tim. Love the way you described.

  2. username4 says:

    Thank you, this is the only solution I have found to work for Civilization 6 mods.