MS SQL Basics Tutorial: Updating data

11.3.1 UPDATE Syntax

The user can use the UPDATE statement to update one or more columns of data values in a table, with the following syntax:

The parameters are described as follows:

SET

The SET clause is used to specify an updated method that replaces the value of the corresponding column. If you omit the WHERE clause, all data in the table is affected. The alias of the table or column specified in the FROM clause cannot be used in the SET clause. column_name

Specifies the column for which you want to update data. The IDENTITY column cannot be updated. Expression

Can be a constant, a variable, an expression, or a subquery that returns a single value. DEFAULT

Specifies that the default value already defined is used as the value of the column. Null

Specifies that a null value is used as a column value. @variable

A predefined variable used to store the value of an expression. Note that the set @variable = Column = expression sets the variables and columns to the same value as the expression, which differs from the Set @variable = column, column = Expression statement, which assigns the value of the column to the variable before it is expressed Value to the column. The remaining parameters and considerations are the same as the DELETE statement.

11.3.2 Update Records

11.3.3 a large number of data updates

When you update data by using update, the original data that is updated is stored in the transaction log. If the table you are updating is particularly large, it is possible to fill the transaction log when the command is not finished. SQL Server then generates an error message and returns the updated data as it is. There are two ways to solve this problem: one is to increase the storage space of the transaction log, but it seems to be uneconomical; the other is to decompose the operation of the UPDATE statement and clean the transaction log in time. For example, the Update command is decomposed into two commands, in which the backup LOG command is inserted to clear the transaction log (see Chapter 15th "Backup and Recovery" for a detailed description of the BACKUP LOG command).

See the full set of "MS SQL Basics Tutorials"