Changes existing data in a table or view.
The UPDATE statement is fully logged; however, partial updates to large value data types using the .WRITE clause are minimally logged. For more information, see "Updating Large Value Data Types" that follows.
UPDATE statements are allowed in the body of user-defined functions only if the table being modified is a table variable.
If an update to a row violates a constraint or rule, violates the NULL setting for the column, or the new value is an incompatible data type, the statement is canceled, an error is returned, and no records are updated.
When an UPDATE statement encounters an arithmetic error (overflow, divide by zero, or a domain error) during expression evaluation, the update is not performed. The rest of the batch is not executed, and an error message is returned.
If an update to a column or columns participating in a clustered index causes the size of the clustered index and the row to exceed 8,060 bytes, the update fails and an error message is returned.
All char and nchar columns are right-padded to the defined length.
The setting of the SET ROWCOUNT option is ignored for UPDATE statements against remote tables and local and remote partitioned views.
If ANSI_PADDING is set to OFF, all trailing spaces are removed from data inserted into varchar and nvarchar columns, except in strings that contain only spaces. These strings are truncated to an empty string. If ANSI_PADDING is set to ON, trailing spaces are inserted. The Microsoft SQL Server ODBC driver and OLE DB Provider for SQL Server automatically set ANSI_PADDING ON for each connection. This can be configured in ODBC data sources or by setting connection attributes or properties. For more information, see SET ANSI_PADDING (Transact-SQL).
A positioned update using a WHERE CURRENT OF clause updates the single row at the current position of the cursor. This can be more accurate than a searched update that uses a WHERE <search_condition> clause to qualify the rows to be updated. A searched update modifies multiple rows when the search condition does not uniquely identify a single row.
Using UPDATE with the FROM Clause
The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic. For example, in the UPDATE statement in the following script, both rows in Table1 meet the qualifications of the FROM clause in the UPDATE statement; but it is undefined which row from Table1 is used to update the row in Table2.
USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
(ColA int NOT NULL, ColB decimal(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2
(ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0), (1, 20.0), (1, 0.0);
GO
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB
FROM dbo.Table2;
The same problem can occur when the FROM and WHERE CURRENT OF clauses are combined. In the following example, both rows in Table2 meet the qualifications of the FROM clause in the UPDATE statement. It is undefined which row from Table2 is to be used to update the row in Table1.
USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
(c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);
GO
CREATE TABLE dbo.Table2
(d1 int PRIMARY KEY NOT NULL, d2 int NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES (1, 10), (1, 20), (2, 30);
GO
DECLARE abc CURSOR LOCAL FOR
SELECT c1, c2
FROM dbo.Table1;
OPEN abc;
FETCH abc;
UPDATE dbo.Table1
SET c2 = c2 + d2
FROM dbo.Table2
WHERE CURRENT OF abc;
GO
SELECT c1, c2 FROM dbo.Table1;
GO
Updating User-defined Type Columns
Updating values in user-defined type columns can be accomplished in one of the following ways:
-
Supplying a value in a SQL Server system data type, as long as the user-defined type supports implicit or explicit conversion from that type. The following example shows how to update a value in a column of user-defined type
Point, by explicitly converting from a string.
UPDATE Cities
SET Location = CONVERT(Point, '12.3:46.2')
WHERE Name = 'Anchorage';
-
Invoking a method, marked as a mutator, of the user-defined type, to perform the update. The following example invokes a mutator method of type
Point named SetXY. This updates the state of the instance of the type.
UPDATE Cities
SET Location.SetXY(23.5, 23.5)
WHERE Name = 'Anchorage';
.gif) Note: |
|---|
|
SQL Server returns an error if a mutator method is invoked on a Transact-SQL null value, or if a new value produced by a mutator method is null.
|
-
Modifying the value of a registered property or public data member of the user-defined type. The expression supplying the value must be implicitly convertible to the type of the property. The following example modifies the value of property
X of user-defined type Point.
UPDATE Cities
SET Location.X = 23.5
WHERE Name = 'Anchorage';
To modify different properties of the same user-defined type column, issue multiple UPDATE statements, or invoke a mutator method of the type.
Updating Large Value Data Types
Use the .WRITE (expression, @Offset, @Length) clause to perform a partial or full update of varchar(max), nvarchar(max), and varbinary(max) data types. For example, a partial update of a varchar(max) column might delete or modify only the first 200 characters of the column, whereas a full update would delete or modify all the data in the column. .WRITE updates that insert or append new data are minimally logged if the database recovery model is set to bulk-logged or simple. Minimal logging is not used when existing values are updated. For more information, see Operations That Can Be Minimally Logged.
The Database Engine converts a partial update to a full update when the UPDATE statement causes either of these actions:
-
Changes a key column of the partitioned view or table.
-
Modifies more than one row and also updates the key of a nonunique clustered index to a nonconstant value.
You cannot use the .WRITE clause to update a NULL column or set the value of column_name to NULL.
@Offset and @Length are specified in bytes for varbinary and varchar data types and in characters for the nvarchar data type. The appropriate offsets are computed for double-byte character set (DBCS) collations.
For best performance, we recommend that data be inserted or updated in chunk sizes that are multiples of 8040 bytes.
If the column modified by the .WRITE clause is referenced in an OUTPUT clause, the complete value of the column, either the before image in deleted.column_name or the after image in inserted.column_name, is returned to the specified column in the table variable. See example G that follows.
To achieve the same functionality of .WRITE with other character or binary data types, use the STUFF (Transact-SQL).
Updating text, ntext, and image Columns
Modifying a text, ntext, or image column with UPDATE initializes the column, assigns a valid text pointer to it, and allocates at least one data page, unless the column is being updated with NULL. If the UPDATE statement could change more than one row while updating both the clustering key and one or more text, ntext, or image columns, the partial update to these columns is executed as a full replacement of the values.
To replace or modify large blocks of text, ntext, or image data, use WRITETEXT or UPDATETEXT instead of the UPDATE statement.
| Important: |
|---|
|
The ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
|
Using INSTEAD OF Triggers on UPDATE Actions
When an INSTEAD OF trigger is defined on UPDATE actions against a table, the trigger is running instead of the UPDATE statement. Earlier versions of SQL Server only support AFTER triggers defined on UPDATE and other data modification statements. The FROM clause cannot be specified in an UPDATE statement that references, either directly or indirectly, a view with an INSTEAD OF trigger defined on it. For more information about INSTEAD OF triggers, see CREATE TRIGGER (Transact-SQL).
Setting Variables and Columns
Variable names can be used in UPDATE statements to show the old and new values affected, but this should be used only when the UPDATE statement affects a single record. If the UPDATE statement affects multiple records, to return the old and new values for each record, use the OUTPUT clause.
Updating a Common Table Expression
When a common table expression (CTE) is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Unambiguous CTE references are required because a CTE does not have an object ID, which SQL Server uses to recognize the implicit relationship between an object and its alias. Without this relationship, the query plan may produce unexpected join behavior and unintended query results. The following examples demonstrate correct and incorrect methods of specifying a CTE when the CTE is the target object of the update operation.
USE tempdb;
GO
-- UPDATE statement with CTE references that are correctly matched.
DECLARE @x TABLE (ID int, Value int);
DECLARE @y TABLE (ID int, Value int);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);
WITH cte AS (SELECT * FROM @x)
UPDATE x -- cte is referenced by the alias.
SET Value = y.Value
FROM cte AS x -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID;
SELECT * FROM @x;
GO
Here is the result set.
ID Value
------ -----
1 100
2 200
(2 row(s) affected)
-- UPDATE statement with CTE references that are incorrectly matched.
USE tempdb;
GO
DECLARE @x TABLE (ID int, Value int);
DECLARE @y TABLE (ID int, Value int);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);
WITH cte AS (SELECT * FROM @x)
UPDATE cte -- cte is not referenced by the alias.
SET Value = y.Value
FROM cte AS x -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID;
SELECT * FROM @x;
GO
Here is the result set.
ID Value
------ -----
1 100
2 100
(2 row(s) affected)
A. Using a simple UPDATE statement
The following examples show how all rows can be affected when a WHERE clause is not used to specify the row or rows to update.
This example updates the values in the Bonus, CommissionPct, and SalesQuota columns for all rows in the SalesPerson table.
USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;
GO
You can also use computed values in an UPDATE statement. The following example doubles the value in the ListPrice column for all rows in the Product table.
USE AdventureWorks ;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO
B. Using the UPDATE statement with a WHERE clause
The following example uses the WHERE clause to specify which rows to update. For example, Adventure Works Cycles sells their bicycle model Road-250 in two colors: red and black. The company has decided to change the color of red for this model to metallic red. The following statement updates the rows in the Production.Product table for all red Road-250 products.
USE AdventureWorks;
GO
UPDATE Production.Product
SET Color = N'Metallic Red'
WHERE Name LIKE N'Road-250%' AND Color = N'Red';
GO
C. Using the UPDATE statement with information from another table
The following example modifies the SalesYTD column in the SalesPerson table to reflect the most recent sales recorded in the SalesOrderHeader table.
USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
ON sp.SalesPersonID = so.SalesPersonID
AND so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE SalesPersonID =
sp.SalesPersonID);
GO
The previous example assumes that only one sale is recorded for a specified salesperson on a specific date and that updates are current. If more than one sale for a specified salesperson can be recorded on the same day, the example shown does not work correctly. The example runs without error, but each SalesYTD value is updated with only one sale, regardless of how many sales actually occurred on that day. This is because a single UPDATE statement never updates the same row two times.
In the situation in which more than one sale for a specified salesperson can occur on the same day, all the sales for each sales person must be aggregated together within the UPDATE statement, as shown in this example:
USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD +
(SELECT SUM(so.SubTotal)
FROM Sales.SalesOrderHeader AS so
WHERE so.OrderDate = (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader AS so2
WHERE so2.SalesPersonID =
so.SalesPersonID)
AND Sales.SalesPerson.SalesPersonID = so.SalesPersonID
GROUP BY so.SalesPersonID);
GO
D. Using UPDATE with the TOP clause
The following example updates the VacationHours column by 25 percent for 10 random rows in the Employee table.
USE AdventureWorks;
GO
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 ;
GO
E. Using UPDATE with the OUTPUT clause
The following example updates the VacationHours column in the Employee table by 25 percent for the first 10 rows. The OUTPUT clause returns the value of VacationHours that exists before applying the UPDATE statement in the DELETED.VacationHours column and the updated value in the INSERTED.VacationHours column to the @MyTableVar table variable.
Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. Note the results in the INSERTED.ModifiedDate column are different from the values in the ModifiedDate column in the Employee table. This is because the AFTER UPDATE trigger that updates the value of ModifiedDate to the current date is defined on the Employee table. However, the columns returned from OUTPUT reflect the data before triggers are fired. For more examples using the OUTPUT clause, see OUTPUT Clause (Transact-SQL).
USE AdventureWorks;
GO
DECLARE @MyTableVar table(
EmpID int NOT NULL,
OldVacationHours int,
NewVacationHours int,
ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.EmployeeID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
F. Using UPDATE with the WITH common_table_expression clause
The following example updates the VacationHours value by 25 percent for all employees reporting directly or indirectly to ManagerID 12. The common table expression returns a hierarchical list of employees who report directly to ManagerID 12 and employees who report to those employees, and so on. Only the rows returned by the common table expression are modified. For more information about recursive common table expressions, see Recursive Queries Using Common Table Expressions.
USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
FROM HumanResources.Employee AS e
WHERE e.ManagerID = 12
UNION ALL
SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
FROM HumanResources.Employee as e
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO
G. Using UPDATE with the .WRITE clause to modify data in an nvarchar(max) column
The following example uses the .WRITE clause to update a partial value in DocumentSummary, an nvarchar(max) column in the Production.Document table. The word components is replaced with the word features by specifying the replacement word, the starting location (offset) of the word to be replaced in the existing data, and the number of characters to be replaced (length). The example also uses the OUTPUT clause to return the before and after images of the DocumentSummary column to the @MyTableVar table variable.
USE AdventureWorks;
GO
DECLARE @MyTableVar table (
DocumentID int NOT NULL,
SummaryBefore nvarchar(max),
SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT INSERTED.DocumentID,
DELETED.DocumentSummary,
INSERTED.DocumentSummary
INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter
FROM @MyTableVar;
GO
H. Using UPDATE with .WRITE to add and remove data in an nvarchar(max) column
The following examples add and remove data from an nvarchar(max) column that has a value currently set to NULL. Because the .WRITE clause cannot be used to modify a NULL column, the column is first populated with temporary data. This data is then replaced with the correct data by using the .WRITE clause. The additional examples append data to the end of the column value, remove (truncate) data from the column and, finally, remove partial data from the column. The SELECT statements display the data modification generated by each UPDATE statement.
USE AdventureWorks;
GO
-- Replacing NULL value with temporary data.
UPDATE Production.Document
SET DocumentSummary = N'Replacing NULL value'
WHERE DocumentID = 1;
GO
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Replacing temporary data with the correct data. Setting @Length to NULL
-- truncates all existing data from the @Offset position.
UPDATE Production.Document
SET DocumentSummary .WRITE(N'Carefully inspect and maintain the tires and crank arms.',0,NULL)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Appending additional data to the end of the column by setting
-- @Offset to NULL.
UPDATE Production.Document
SET DocumentSummary .WRITE (N' Appending data to the end of the column.', NULL, 0)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Removing all data from @Offset to the end of the existing value by
-- setting expression to NULL.
UPDATE Production.Document
SET DocumentSummary .WRITE (NULL, 56, 0)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Removing partial data beginning at position 9 and ending at
-- position 21.
UPDATE Production.Document
SET DocumentSummary .WRITE ('',9, 12)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary
FROM Production.Document
WHERE DocumentID = 1;
GO
I. Using UPDATE with OPENROWSET to modify a varbinary(max) column
The following example replaces an existing image stored in a varbinary(max) column with a new image. The OPENROWSET function is used with the BULK option to load the image into the column. This example assumes that a file named Tires.jpg exists in the specified file path.
USE AdventureWorks;
GO
UPDATE Production.ProductPhoto
SET ThumbNailPhoto = (
SELECT *
FROM OPENROWSET(BULK 'c:\Tires.jpg', SINGLE_BLOB) AS x )
WHERE ProductPhotoID = 1;
GO
J. Using UPDATE with SELECT @local_variable and a compound operator
The following example uses the variable @NewPrice to increment the price of all red bicycles by taking the current price and adding 10 to it.
USE AdventureWorks;
GO
DECLARE @NewPrice int = 10;
UPDATE Production.Product
SET ListPrice += @NewPrice
WHERE Color = N'Red';
GO
K. Using UPDATE in a stored procedure
The following example uses an UPDATE statement in a stored procedure. The procedure takes one input parameter, @NewHours. The parameter value is used in the UPDATE statement to update the column VacationHours in the table HumanResources.Employee. The CASE expression is used in the SET clause to conditionally determine the value that is set for VacationHours. When the employee is paid hourly (SalariedFlag = 0), VacationHours is set to the current number of hours plus the value specified in @NewHours; otherwise, VacationHours is set to the value specified in @NewHours.
USE AdventureWorks;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint,
@RowCount int OUTPUT
AS
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours =
( CASE
WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
ELSE @NewHours
END
)
WHERE CurrentFlag = 1;
SET @RowCount = @@ROWCOUNT;
GO
-- Execute the stored procedure and return the number of rows updated to the variable @RowCount
DECLARE @RowCount int;
EXECUTE HumanResources.Update_VacationHours 40, @RowCount OUTPUT;
SELECT @RowCount AS RowCount;