Click here to Skip to main content
15,792,841 members
Articles / Database Development / SQL Server / SQL Server 2014

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

Rate me:
Please Sign up or sign in to vote.
2.92/5 (8 votes)
20 Mar 2015CPOL1 min read 10.7K   5   3
Yes, Virginia, You Can Do This All With One Simple SQL Statement


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.


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)

Catch ex As Exception
    (Handle error here)
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!


  • 20th March, 2015: Initial version


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

QuestionUse the MERGE command Pin
szalkovics20-Mar-15 13:37
szalkovics20-Mar-15 13:37 
AnswerRe: Use the MERGE command Pin
furytrader20-Mar-15 15:57
furytrader20-Mar-15 15:57 
GeneralDon't use MERGE, don't use IF EXISTS ... Pin
Christophe Van Olmen23-Mar-15 23:26
professionalChristophe Van Olmen23-Mar-15 23:26 
I wouldn't use MERGE in this instance, overkill for what you're trying to accomplish. MERGE has its uses and being aware of its existence is important, but you should also know that it has a bit of history as slow and victim of some strange bugs (also the syntax is rather strange). It's very nice when you have a large dataset that you need to manipulate in some way, but for single line updates, use something else.

I wouldn't use IF EXISTS in this case, that's making the SQL engine do more work than necessary. Instead, start by using transactions, either in a SQL code block, or your C# code. Then just do the update and check how many rows you updated (SQL Server: @@ROWCOUNT). If zero, then insert, otherwise you're done.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.