Skip to content

SQL Server Tutorial - UPDATE Statement 101

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
UPDATE [dbo].[Product]
SET [LastInventoryDate] = GETDATE()
  • Using UPDATE to Update Rows that Meet a Condition
UPDATE [dbo].[User]
SET [IsLockedOut] = 1
WHERE [PasswordTries] >= 5
  • Using UPDATE to Update a Column Based on the Value of Another Column in the Same Row
UPDATE [dbo].[User]
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] ( 
    [UserName]       VARCHAR(20),
    [Password]       VARBINARY(100),
    [PasswordTries]  INT DEFAULT(0),
)

INSERT INTO [dbo].[User3] ( [UserName], [Password], [PasswordTries] )
VALUES ( 'sqlserver', CAST('$QLP9ssw0rd' AS VARBINARY(100)), 10 )

UPDATE [dbo].[User3]
SET [PasswordTries] = DEFAULT
  • Using UPDATE to set the Column Values Using Another Table
UPDATE [dbo].[Product]
SET [UnitPrice] = [dbo].[ProductBackup].[UnitPrice]
FROM [dbo].[Product] INNER JOIN [dbo].[ProductBackup]
  ON [dbo].[Product].[ProductID] = [dbo].[ProductBackup].[ProductID]