The UPDATE statement is a Data Manipulation Language (DML) that can change data values in single rows, or all the rows in a table or view. An UPDATE statement referencing a table or view can change the data in only one base table at a time.
The UPDATE statement has the following major clauses:
- SET - Contains a comma-separated list of the columns to be updated and the new value for each column, in the form of < column_name > = < expression >. The value supplied by the expressions includes items such as constants, values selected from a column in another table or view, or values calculated by a complex expression.
- FROM - Identifies the tables or views that supply the values for the expressions in the SET clause, and optional JOIN conditions between the source tables or views.
- WHERE - Specifies the search condition identifying the rows from the source table to be updated. If no WHERE clause is specified, all rows in the table are updated.
Here are a few examples of how to use the UPDATE statement:
Using UPDATE to Update All Rows
SET [LastInventoryDate] = GETDATE()
Using UPDATE to Update Rows that Meet a Condition
SET [IsLockedOut] = 1
WHERE [PasswordTries] >= 5
Using UPDATE to Update a Column Based on the Value of Another Column in the Same Row
SET [AgeEstimate] = DATEDIFF(YY, [BirthDate], GETDATE())
Using UPDATE to Update a Certain Number of Rows Only
UPDATE TOP (20) [dbo].[Candidates]
SET [IsWinner] = 1
Using UPDATE to Set the Value of a Column to Its Default Value
CREATE TABLE [dbo].[User3] (
[PasswordTries] INT DEFAULT(0),
INSERT INTO [dbo].[User3] ( [UserName], [Password], [PasswordTries] )
VALUES ( 'sqlserver', CAST('$QLP9ssw0rd' AS VARBINARY(100)), 10 )
SET [PasswordTries] = DEFAULT
Using UPDATE to set the Column Values Using Another Table
SET [UnitPrice] = [dbo].[ProductBackup].[UnitPrice]
FROM [dbo].[Product] INNER JOIN [dbo].[ProductBackup]
ON [dbo].[Product].[ProductID] = [dbo].[ProductBackup].[ProductID]