SQL insert row if doesn’t exist

Posted by Pourya Shahroudi on Apr 16, 2010 in Software Development | 1 comment

There are cases in which you like to insert rows into the database only if it doesn’t exist. There are databases that allow you to insert conditions into the SQL to test the number of rows in the table and insert the new row only of there are no rows currently in the table. But unfortunately you cant do that with many other databases.
Here is a simple way to use regular SQL notations to insert rows into the database table only if it doesn’t exit.
lets assume you have a table called users with the following schema:

name(varchar)

family(varchar)

email(varchar)

lets assume we are interested in inserting a row with the following values only if it doesn’t exist in the database table:
name: Pourya
family: Shahroudi
email: something@something.com

INSERT INTO USERS(name,family,email)
SELECT 'Pourya', 'Shahroudi','something@something.com' FROM USERS
WHERE NOT EXISTS(
    SELECT name,family,emial FROM USERS
    WHERE name='Pourya'
    AND family='Shahroudi' and email='something@something.com'
) and ROWNUM = 1;

As you can see this query does a SELECT on the database table with the values we want to insert if this row doesn’t exist then they are inserted into the database table.
NOTE: “ROWNUM = 1” is extremely important in this query if you don’t have this included the query will try to insert as many records as you currently have in the table into the table.