INSERT IF NOT EXISTS with NULL value

exists, insert, sql, sqlite

Solution

Create a `UNIQUE` constraint on `tags.name` and use this:

INSERT  OR IGNORE
INTO    tags (id, name)
VALUES  (NULL, 'tagsName')

If for some reason you can't or don't want to do this, use this:

INSERT
INTO    tags (id, name)
SELECT  NULL, 'tagsName'
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    tags
        WHERE   name = 'tagsName'
        )

Problem

I have two tables: tags and linking table photos_tags. I want to add a new tag, so I check if the tag is already in the tags table and if not I insert one. My tables look something like this: tags(id, name), photos_tags(photos_id, tags_id). Now I tried to do this with: ``` IF NOT EXISTS (SELECT * FROM tags WHERE name=*tagsName*) INSERT INTO tags VALUES (NULL, *tagsName*); --NULL used for autonumbering ``` I've also tried: ``` INSERT INTO tags ( SELECT NULL, *tagsName* WHERE NOT EXISTS ( SELECT * FROM tags WHERE name=*tagsName* ) ); ``` Both statements result in syntax errors: - in the first statement near `if` - in the second statement near `select` What should my query look like?

Original source