SQL to determine whether a single piece of data is executed by key in an Update or Insert

Page update date :
Page creation date :

In a process such as Update if there is a key or Insert if there is no key, a select is usually performed based on the key, and the process is determined by checking whether there is one case. SQL Server 2008 also adds a "merge" statement that allows you to perform an Update and an Insert (and then Delete) with a single statement. However, this is a comparison of two tables and is not suitable for single data.

Actually, there is a way to easily update a row by determining Update and Insert without having to check the number of records with a select statement.

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

The key word here is "@@ROWCOUNT". The @@ROWCOUNT can retrieve the number of rows to which the previous SQL was applied. The preceding code executes the Update statement first, and if it has been updated, @@ROWCOUNT returns 1 or greater, so it does not execute the next Insert statement. Conversely, if no row is updated in the Update statement, @@ROWCOUNT returns 0, so execute Insert by IF statement judgment.

Since the above SQL is a template, replace the column names and values to be updated at the appropriate time. @value is just a variable name, so replace this with the value you want to update.