SQL Server Helper - Tip of the Day

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]