You use the UPDATE
statement to modify an existing table data.
To execute the UPDATE
statement, a user must have at least an UPDATE
permission assigned on the target table.
The following is the basic syntax for the UPDATE
statement:
[WITH <common_table_expression> [, ...n]] UPDATE [TOP (expression) [PERCENT]] table_name | view_name | rowset_function | common_table_expression [WITH table_hint] SET column_name = {expression | DEFAULT | NULL} [ ,...n ] <outputclause> FROM < table_name | view_name | common_table_expression> WHERE <search_condition>
The following are the arguments of the UPDATE
statement:
WITH
: This keyword specifies the CTE that you can reference in your UPDATE
statementTOP
: You specify this keyword to only update a specific number or percent of rows from the top of the querytable_name | view_name | rowset function | common_table_expression
: This specifies the name of the table, view, rowset function, or CTE that contains the data to be updatedSET
: This specifies the name of the column or columns to be updatedFROM
: This keyword specifies the name of the table, view, or CTE from which you are taking the dataWHERE
: This keyword specifies the search condition to identify which records are to be updatedWITH table_hints
: You use this keyword to specify one or more table hints for the destination tableoutput_clause
: You use this to identify the rows affected by the UPDATE
statementThis section describes the basic forms of the UPDATE
statement.
The following UPDATE
statement updates the list price of a single product in the Production.Product
table:
USE [AdventureWorks2012]; GO UPDATE [Production].[Product] SET [ListPrice] = 1670 WHERE [ProductID] = 13; GO
Suppose you want to increase the list price by 25 percent only for products whose list price is less than $1,000. To accomplish this, you need to run the following UPDATE
statement in the SSMS 2014 Query Editor:
USE [AdventureWorks2012]; GO UPDATE [Production].[Product] SET [ListPrice] = [ListPrice] + ([ListPrice] * 0.25) WHERE [ListPrice] < 1000; GO
Another example, the following UPDATE
statement, updates the list price to $200 for products whose current price list is $0.0:
USE [AdventureWorks2012]; GO UPDATE [Production].[Product] SET [ListPrice] = 200.0 WHERE [ListPrice] = 0.0; GO
3.21.159.82