SQL that determines whether a single data is executed in Update or Insert by key

Page updated :

Actions such as Update if there is a key, insert if not are typically performa select based on the key, and check to see if there is one to determine what to do. Sql Server 2008 also addsa mergestatement, allowing you to execute Update and Insert (delete) in a single statement. However, this is done by comparing the two tables and is not suitable for a single data.

In fact, there is a way to easily update the row by determining Update and Insert without having to look at the number in the Select statement.

update [TableName]
set [ColumnName] = @value
--    : 他更新 SQL
where [KeyColumnName] = @value
--    : 他キー比較
if @@ROWCOUNT = 0
insert into [TableName]
(
  [ColumnName]
--  : 他更新 SQL
)
values
(
  @value
--  : 他更新値
)

The key word here is@@ROWCOUNT. @@ROWCOUNT can get the number of rows that the previous SQL was applied to. If the preceding code executes the Update statement first, and if it is updated, @@ROWCOUNT returns 1 or more, so it does not execute the next Insert statement. Conversely, if no one row is updated in the Update statement, @@ROWCOUNT returns 0, so insert is executed in the IF statement determination.

Because the ABOVE SQL is a template, replace the timely column name and the value to update. @value is just a variable name, so replace it with the value you want to update.