Insert or Update With a Single SQL Statement

Ever come across the situation while developing data-driven web applications when you needed to create a new record if one doesn't exist, but if one does exist, then you need to update it instead?

I certainly have, and I must admit with some shame that in the past I've handled it in the most obvious, and least elegant and efficient way, by

querying SQL for the existence of the record,
checking the result set in my code by looping and assigning a variable,
checking the variable for a value, and if one doesn't exist, then doing the insert.
Otherwise, doing the update.

There are a couple problems here. First, it's a lot more code than necessary. Second, it requires two calls to SQL instead of one.

You can eliminate this by making SQL do the conditional logic for you, via IF EXISTS. Here's the sample:

IF EXISTS(
 SELECT 1
 FROM MY_TABLE
 WHERE ITEM='somevalue' AND ENTERDATE='12/31/1999')
    --Update Statement
    UPDATE MY_TABLE
    SET ITEM='anothervalue'
    WHERE ITEM='somevalue' AND ENTERDATE='12/31/1999'
ELSE
    --Insert Statement
    INSERT INTO MY_TABLE
    (ITEM, ENTERDATE)
    VALUES
    ('somevalue', '12/31/1999')

EXISTS lets you run a query statement, and if a value is returned, it outputs true. Otherwise, it outputs false. Couple that to IF/ELSE, and you can see how useful this particular SQL clause is.

The query inside EXISTS returns 1 if the parameters in the WHERE clause match, and returns nothing otherwise. What we return really doesn't matter. We're interested mainly in the parameters. If the parameters match something, then we will update them. Otherwise (ELSE), we insert them into the table.

Pretty simple. We just add our code parameters to the above statement (if your language uses parameters, e.g. Perl or C#), and send it on its way. One SQL call, and a lot less logic.

Update: I should have been clearer. This is TSQL, and will not work, in say, MySQL. (Thanks anonymous commenter!)