More nifty SQL: insert if not exists

Quite often you want to insert a record into a table, but only if it doesn’t already exist. OK, you could first do a SELECT query to check for existence, and then do the insert, but it’s clumsy and raises the slim possibility of a race condition (what if someone inserted or deleted that record between the check and the insert?). Every RDBMS seems to handle this differently (and some not at all). In SQL Server, you can do this:
IF NOT EXISTS (SELECT acolumn from atable where column1 = 1 and column2 = 'text') INSERT INTO atable(column1,scolumn2) VALUES(1,'text')

And in MySQL:
INSERT INTO atable(column1,column2) SELECT 1, 'text' FROM DUAL WHERE NOT EXISTS (SELECT column1 FROM atable WHERE column1=1 AND column2='text' LIMIT 1)

Notice that in MySQL you have to use the dummy DUAL table (which suggests to me that this syntax might work in Oracle too; I haven’t tried it).

This entry was posted in Databases and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>