Microsoft SQL: Increase Existing Value In Row Or Add New Row If None Exists

Introduction

This is not a blockbuster SQL tip, but since I didn't find anything like this already online, I wanted to pass this along so that others can quickly port my solution to their project.

Background

Let's imagine that you are analyzing different sources for news articles and you're counting how many times a particular source publishes an article containing a particular keyword. Consequently, your database has a table with the following three columns:

1. SourceID (int)
2. Keyword (string)
3. KeywordCount (int)

For example, in a particular row, the SourceID might equal "12345". The keyword might equal "gold" and the keyword count might equal 100.

Now, let's imagine that you analyze another 10 articles from this source and the word "gold" appears in 6 of those articles. You want to increase the KeywordCount value by 6.

However, it's also possible that you could have a totally new keyword for this source (for example, "silver") and instead of adding 6 to an existing row, you want to create a new row for this keyword (and add the count value).

Here's an easy way to do it with one line of Microsoft SQL.

Using the Code

Let's imagine that you have the following values:

tempSourceID = int
tempKeyword = String
tempKeywordCount = int

You would use the following SQL call:

Dim upsertCommand As SqlCommand = connection.CreateCommand

upsertCommand.CommandText = "IF EXISTS (SELECT 1 FROM (Table_Name) _
WHERE SourceID = @TempSourceID) UPDATE (Table_Name) SET KeywordCount = _
(KeywordCount + @TempKeywordCount) WHERE SourceID=@TempSourceID AND _
Keyword = @TempKeyword ELSE INSERT INTO (Table_Name) _
(SourceID, Keyword, KeywordCount) VALUES (@TempSourceID, @TempKeyword, @TempKeywordCount)"

upsertCommand.Parameters.AddWithValue("@TempKeywordCount", tempKeywordCount)
upsertCommand.Parameters.AddWithValue("@TempSourceID", tempSourceID)
upsertCommand.Parameters.AddWithValue("@TempKeyword", tempKeyword)

Try
    upsertCommand.ExecuteNonQuery()
Catch ex As Exception
    (Handle error here)
Finally
     connection.Close()
End Try

Points of Interest

I saw a number of other attempts at doing this which seemed to require a lot more commands to accomplish what I've done above. Any feedback is appreciated!

History

• 20^th March, 2015: Initial version