UPDATE Statement (Transact-SQL) - MSDN Documentation

UPDATE (Transact-SQL)

Updates existing rows in a table or view.

Syntax


UPDATE [ database_name . [ schema_name ] . | schema_name . ] object_name
    SET { <column> = { expression | NULL }
        | <object_name>.<column> = { expression | NULL }
        } [ ,...n ]
    [ FROM <from_clause> ]
    [ WHERE <search_condition> ]
    [ OPTION ( <query_option> [ ,...n ] ) ]

<column> ::= { column_name | { table_alias } . column_name }

<from_clause> ::=
    { <table_source> } [ ,...n ]

<table_source> ::=
    { <table_or_view_source>
    | <joined_table_source>
    }

<table_or_view_source> ::=
    { input_table_or_view_name [ [ AS ] <table_alias> ] }

<joined_table_source> ::=
    { <table_source> <join_type> <table_source>
        ON <search_condition>
    }

<join_type> ::=
    [ { INNER | { LEFT | RIGHT | FULL } [ OUTER ] } ]
    [ { CROSS | INNER } JOIN ]
    [ { LEFT | RIGHT } { OUTER | SEMI | ANTI } JOIN ]
    [ { FULL [ OUTER ] } JOIN ]

<search_condition> ::= <logic_expression>

-- ... other syntax elements ...

Description

The UPDATE statement is used to modify existing records in a table. You can update specific columns or all columns of the selected rows. The WHERE clause is crucial for specifying which rows should be updated. If the WHERE clause is omitted, all rows in the table will be updated, which is often an undesirable outcome.

Arguments

database_name.schema_name.object_name or schema_name.object_name: Specifies the table or view to update.
SET <column> = { expression | NULL }: Assigns a value to a column. The value can be a literal, an expression, or NULL.
FROM <from_clause>: Used when the UPDATE statement involves joining multiple tables to determine which rows to update.
WHERE <search_condition>: Filters the rows to be updated. Only rows that satisfy the condition are modified.
OPTION ( <query_option> ): Specifies query hints to influence the execution plan.

Examples

Basic Update

Update the ListPrice of a product.


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

Update with JOIN

Update the SalesYTD for a salesperson based on their sales orders.


UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + soh.SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS soh
    ON sp.BusinessEntityID = soh.SalesPersonID
WHERE sp.TerritoryID = 5 AND soh.OrderDate >= '2023-01-01';

Update with a Subquery

Update the ManagerID for employees in a specific department.


UPDATE HumanResources.Employee
SET ManagerID = (SELECT BusinessEntityID FROM HumanResources.Employee WHERE JobTitle = 'VP of Engineering')
WHERE DepartmentID = 3;

Microsoft Docs