UPDATE (Transact-SQL)

Modifies existing rows in a table or views.

Syntax


UPDATE [ database_name . [ schema_name ] . | schema_name . ] object_name
    [ { <object\_identifier> } ]
    [ WITH ( <update_option> [ , ...n ] ) ]
    {  |  |  |  }
    SET
        { column = { expression | default | NULL } [ , ...n ] }
        [ FROM
            {  [ , ...n ] }
        ]
        [ WHERE
            {  }
        ]
        [ OPTION ( <query_hint> [ , ...n ] ) ]

Description

The UPDATE statement modifies data in an existing table. You can update specific rows by using the WHERE clause, or you can update all rows in a table. You can update one or more columns at a time.

The UPDATE statement can modify data in a table that is part of a view. However, the update must be applied to the base table or tables that the view is based on.

Updating Views

You can use UPDATE on a view, provided that the view is updatable. A view is updatable if it meets certain criteria, such as:

The view is based on a single table.
The view does not use aggregate functions, DISTINCT, or derived columns.
All columns being updated are directly mapped to columns in the base table.

Parameters

This section is a placeholder for detailed parameter descriptions. In a real documentation page, this would list and explain each clause and its options.

object_name: The table or view to update.
SET: Specifies the column(s) to update and the new value(s).
WHERE: Filters the rows to be updated. If omitted, all rows are updated.
FROM: Allows joining with other tables to determine which rows to update.

Return Value

The UPDATE statement returns the number of rows affected by the statement.

Important

Always test your UPDATE statements with a WHERE clause carefully. An UPDATE statement without a WHERE clause will modify all rows in the specified table.

Permissions

Requires UPDATE permission on the target table or view.

Examples

Example 1: Update a single row

This example updates the ListPrice for a specific product in the Production.Product table.


UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductID = 10;

Example 2: Update multiple rows

This example increases the StandardHours for all employees in the 'Engineering' department.


UPDATE HumanResources.Employee
SET StandardHours = StandardHours + 8
WHERE Department = 'Engineering';

Example 3: Update multiple rows using a JOIN

This example updates the SalesYTD for salespeople who are associated with a specific territory.


UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + s.OrderTotal
FROM Sales.SalesPerson sp
JOIN
    (
        SELECT BusinessEntityID, SUM(TotalDue) AS OrderTotal
        FROM Sales.SalesOrderHeader
        WHERE OrderDate >= '2023-01-01'
        GROUP BY BusinessEntityID
    ) AS s ON sp.BusinessEntityID = s.BusinessEntityID
WHERE sp.TerritoryID = 5;

Microsoft Learn