Updating data in SQL Server database tables

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 statement
  • TOP: You specify this keyword to only update a specific number or percent of rows from the top of the query
  • table_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 updated
  • SET: This specifies the name of the column or columns to be updated
  • FROM: This keyword specifies the name of the table, view, or CTE from which you are taking the data
  • WHERE: This keyword specifies the search condition to identify which records are to be updated
  • WITH table_hints: You use this keyword to specify one or more table hints for the destination table
  • output_clause: You use this to identify the rows affected by the UPDATE statement

The UPDATE statement examples

This section describes the basic forms of the UPDATE statement.

Example 1 – updating a single row

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

Example 2 – updating multiple rows

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
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.21.159.82