SQL to determine whether a single piece of data is executed by key in an Update or Insert
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] ( [ColumnName] -- : 他更新 SQL ) values ( @value -- : 他更新値 )
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.