UPDATE Statement (Transact-SQL)

Applies to: SQL Server (all supported versions)

Modifies existing records in a table.

Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

Parameters

• table_name: The name of the table to update.
• column1, column2, ...: The names of the columns to update.
• value1, value2, ...: The new values to assign to the specified columns. These can be literals, expressions, or column values from other tables.
• condition: An optional expression that specifies which rows to update. If omitted, all rows in the table will be updated.

Description

The UPDATE statement is used to modify the existing data in one or more columns of a table. You can update a single row, multiple rows, or even all rows in a table, depending on the WHERE clause you provide.

Examples

1. Update a single column for a specific row:

This example updates the EmailAddress for the employee with BusinessEntityID 10.

UPDATE Person.Person
SET EmailAddress = 'new.email@example.com'
WHERE BusinessEntityID = 10;

2. Update multiple columns for specific rows:

This example updates the RateChangeDate and Rate for all employees in the 'Tool Design' job title.

UPDATE HumanResources.EmployeePayHistory
SET RateChangeDate = GETDATE(),
    Rate = 35.50
FROM HumanResources.EmployeePayHistory eph
INNER JOIN HumanResources.JobCandidate jc
    ON eph.BusinessEntityID = jc.BusinessEntityID
WHERE eph.Rate < 25.00
  AND jc.JobTitle = 'Tool Designer';

3. Update rows based on another table:

This example increases the ListPrice by 10% for products whose ProductID exists in the Production.ProductCostHistory table and had a EndDate before a certain date.

UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductID IN (
    SELECT ProductID
    FROM Production.ProductCostHistory
    WHERE EndDate < DATEADD(month, -1, GETDATE())
);

See Also