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:
For example, in a particular row, the
SourceID might equal "
12345". The keyword might equal "
gold" and the keyword count might equal
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
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)"
Catch ex As Exception
(Handle error here)
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 member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.