Insert record only if it does not exist in table without unique index

Reference: http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table/#3164741

Supposing we have a database table named distributor and we wish to insert a record only if there is no existing record with the same name and country code. The table only has a PRIMARY key but no UNIQUE index on (name, country_code) – think “legacy” and “no privileges to modify database” 😛

The following SQL can be used in MySQL. name is selected in the NOT EXISTS clause in case there is no id column. Aliasing of the created and updated columns in the 2nd SELECT statement is needed else having the same values for the 2 columns will cause a “Duplicate column name” error:

INSERT INTO distributor (name, country_code, is_suspended, created, updated)
    SELECT * FROM (
        SELECT 'FooBar' AS name, 'SG' AS country_code, 0 AS is_suspended,
        '2017-04-18 12:30:00' AS created, '2017-04-18 12:30:00' AS updated
    ) AS tmp
    WHERE NOT EXISTS (
        SELECT name FROM distributor 
        WHERE name = 'FooBar' AND country_code = 'SG'
        LIMIT 1
    ) 
    LIMIT 1;

Try running the same query a few times – only 1 record will be inserted 🙂