The UPDATE statement in SQL has perplexing and potentially confusing syntax. Typically mastered by expert DBA-s and SQL developers, and the rest of the database community uses it in an insecure manner: never sure if it works as intended. The following simple examples demonstrate some of the issues with the UPDATE syntax and offer solutions.
First we create a new table for experimentation from the AdventureWorks database and perform a demo inner join UPDATE on the new table.
USE tempdb;
SELECT ProductID,
ProductName = Name,
COST = StandardCost,
ListPrice,
Color,
ModifiedDate
INTO Product
FROM AdventureWorks2008.Production.Product
WHERE ListPrice > 0.0
AND Color IS NOT NULL
GO
-- (245 row(s) affected)
-- UPDATE with INNER JOIN - QUICK SYNTAX
UPDATE p
SET p.ModifiedDate = DATEADD(dd,1,awp.ModifiedDate)
FROM Product p
INNER JOIN AdventureWorks2008.Production.Product awp
ON p.ProductID = awp.ProductID
WHERE p.Color = 'Yellow'
-- (36 row(s) affected)
SELECT TOP (5) * FROM Product ORDER BY ProductID
GO
/* Partial Results
ProductID ProductName Cost ListPrice Color
680 HL Road Frame - Black, 58 1059.31 1431.50 Black
706 HL Road Frame - Red, 58 1059.31 1431.50 Red
707 Sport-100 Helmet, Red 13.0863 34.99 Red
708 Sport-100 Helmet, Black 13.0863 34.99 Black
709 Mountain Bike Socks, M 3.3963 9.50 White
*/
We shall proceed and update ALL the rows in the Product table. We increase the ListPrice by 5%.
UPDATE Product
SET ListPrice = ListPrice * 1.05
GO
SELECT TOP (5) * FROM Product ORDER BY ProductID
GO
/* Results
ProductID ProductName Cost ListPrice Color
680 HL Road Frame - Black, 58 1059.31 1503.075 Black
706 HL Road Frame - Red, 58 1059.31 1503.075 Red
707 Sport-100 Helmet, Red 13.0863 36.7395 Red
708 Sport-100 Helmet, Black 13.0863 36.7395 Black
709 Mountain Bike Socks, M 3.3963 9.975 White
*/
If we look in messages window we see (245 row(s) affected) for the UPDATE statement. Those are all the rows in the Product table.
The biggest danger with the UPDATE statement is that without proper FILTERING all the rows in the table are updated. UPDATE is as destructive as DELETE if happens unintentionally.
The following UPDATE statement applies a WHERE filter for a single row UPDATE of 10% increase in the ListPrice.
/* ALL rows updated to 599 due to lack of WHERE filtering
UPDATE Product
SET ListPrice = 599.00
*/
UPDATE Product
SET ListPrice = ListPrice * 1.10
WHERE ProductID = 680
GO
-- Messages: (1 row(s) affected)
SELECT TOP (5) * FROM Product ORDER BY ProductID
GO
/* Results
ProductID ProductName Cost ListPrice Color
680 HL Road Frame - Black, 58 1059.31 1653.3825 Black
706 HL Road Frame - Red, 58 1059.31 1503.075 Red
707 Sport-100 Helmet, Red 13.0863 36.7395 Red
708 Sport-100 Helmet, Black 13.0863 36.7395 Black
709 Mountain Bike Socks, M 3.3963 9.975 White
*/
-- SQL UPDATE CTE - UPDATE underlying table through CTE
;WITH CTE
AS (SELECT Price = ListPrice
FROM Product
WHERE ListPrice > 1000.0)
UPDATE CTE
SET Price = Price * 1.05
GO
-- (86 row(s) affected)
-- SQL ANSI Standard UPDATE
UPDATE Product
SET ListPrice = (SELECT p8.ListPrice * 1.25
FROM AdventureWorks2008.Production.Product p8
WHERE Product.ProductID = p8.ProductID)
WHERE EXISTS (SELECT *
FROM AdventureWorks2008.Production.Product p8
WHERE Product.ProductID = p8.ProductID
AND Product.ListPrice > 1000.0);
-- (86 row(s) affected)
The confusion factor: the following two statements are equivalent to each other. The second statement is really perplexing. Why does it work with two references to the same table without a SELF-JOIN?
UPDATE Product
SET ListPrice = ListPrice * 1.10
WHERE ProductID = 680
GO
-- Messages: (1 row(s) affected)
UPDATE Product
SET ListPrice = ListPrice * 1.10
FROM Product
WHERE ProductID = 680
GO
-- Messages: (1 row(s) affected)
To take the confusion factor out from the UPDATE statement:
1. We have to alias the table to be updated in the FROM clause
2. We have to use the use the table alias after the UPDATE
NOTE: only the FROM table(s) can be aliased. Example:
Aliasing attempt after UPDATE gives an error:
UPDATE Product p
SET ListPrice = ListPrice * 1.10
FROM p
WHERE ProductID = 680
GO
/*
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'p'.
*/
Correct syntax with aliasing:
UPDATE p SET ListPrice = ListPrice * 1.10
FROM Product p
WHERE ProductID = 680
GO
-- Messages: (1 row(s) affected)
It is clear that we are updating the single-referenced Product table. The table aliasing solution becomes real important in complex updates when multiple tables are involved.
If you want to be confident about an UPDATE query, you must use aliases after UPDATE and after SET. The following complex query demonstrate how aliasing removes any doubt of what is going to happen in the UPDATE query.
USE tempdb;
SELECT * INTO SalesPerson from AdventureWorks.Sales.SalesPerson
SELECT * INTO SalesOrderHeader from AdventureWorks.Sales.SalesOrderHeader
SELECT * INTO SalesPerson from AdventureWorks.Sales.SalesPerson
GO
-- UPDATE all rows
UPDATE SalesPerson SET SalesYTD = 0.0
GO
-- (17 row(s) affected)
SELECT TOP (5) SalesPersonID, SalesYTD FROM SalesPerson ORDER BY SalesPersonID
/* Partial results
SalesPersonID SalesYTD
268 0.00
275 0.00
276 0.00
277 0.00
278 0.00
*/
UPDATE sp
SET sp.SalesYTD = sp.SalesYTD + sod.SubTotal
FROM SalesPerson sp
JOIN SalesOrderHeader sod
ON sp.SalesPersonID = sod.SalesPersonID
AND sod.OrderDate = (SELECT MAX(OrderDate)
FROM SalesOrderHeader
WHERE SalesPersonID = sp.SalesPersonID);
GO
-- (17 row(s) affected)
SELECT TOP (5) SalesPersonID, SalesYTD FROM SalesPerson ORDER BY SalesPersonID
/* Partial results
SalesPersonID SalesYTD
268 35331.66
275 32700.6053
276 713.796
277 30163.638
278 +27995.85
*/
(17 row(s) affected)
The following Microsoft SQL Server 2008 T-SQL UPDATE syntax is using the new MERGE statement.
---------------
-- SQL UPDATE applying MERGE
--------------
USE tempdb;
SELECT * INTO Product FROM AdventureWorks2008.Production.Product
GO
-- (504 row(s) affected)
SELECT TOP (3) ProductID, ListPrice FROM Product
WHERE Color is not null and ListPrice > 0
/* ProductID ListPrice
680 1431.50
706 1431.50
707 34.99 */
MERGE INTO tempdb.dbo.Product AS Target
USING (SELECT ProductID, ListPrice
FROM tempdb.dbo.Product
WHERE Color is not null AND ListPrice > 0.0)
AS Source (ProductID, ListPrice)
ON Target.ProductID = Source.ProductID
WHEN MATCHED THEN
UPDATE SET ListPrice= Source.ListPrice * 1.1;
-- (245 row(s) affected)
/* ProductID ListPrice
680 1574.65
706 1574.65
707 38.489 */
--------------
The following example demonstrates UPDATE from a different database. Both tables aliased in the FROM clause.
USE CopyOfAdventureWorks;
SELECT IsOnlyStateProvinceFlag FROM Person.StateProvince
WHERE Name='Virgin Islands';
GO
-- 1
-- SQL update - one table only, set flag to 0
UPDATE Person.StateProvince
SET IsOnlyStateProvinceFlag=0
WHERE Name='Virgin Islands';
GO
-- (1 row(s) affected)
SELECT IsOnlyStateProvinceFlag FROM Person.StateProvince
WHERE Name='Virgin Islands';
GO
-- 0
-- SQL update, two tables, one of the tables in a different database
-- SQL 3-part reference
UPDATE sp
SET sp.IsOnlyStateProvinceFlag = awsp.IsOnlyStateProvinceFlag
FROM Person.StateProvince sp
INNER JOIN AdventureWorks.Person.StateProvince awsp
ON sp.StateProvinceID = awsp.StateProvinceID
WHERE sp.Name='Virgin Islands';
GO
-- (1 row(s) affected)
SELECT IsOnlyStateProvinceFlag FROM Person.StateProvince
WHERE Name='Virgin Islands';
GO
-- 1
The following complex example demonstrates UPDATE with an INNER JOIN to SELECT - GROUP BY subquery (derived table DG) .
USE tempdb
GO
SELECT * into dbo.Department
FROM AdventureWorks.HumanResources.Department
ALTER TABLE dbo.Department
ADD NoOfEmployees int not null default (0)
GO
UPDATE DE
SET DE.NoOfEmployees =DG.DeptEmployees,
DE.ModifiedDate = CURRENT_TIMESTAMP
FROM dbo.Department DE
JOIN (SELECT D.DepartmentID, COUNT(*) AS DeptEmployees
FROM AdventureWorks.HumanResources.EmployeeDepartmentHistory EDH
JOIN dbo.Department D
ON d.DepartmentID = EDH.DepartmentID
and EDH.EndDate is null
JOIN AdventureWorks.HumanResources.Employee E
ON EDH.EmployeeID = E.EmployeeID
GROUP BY D.DepartmentID ) DG
ON DE.DepartmentID = DG.DepartmentID
GO
-- (16 row(s) affected)
SELECT Department=Name, NoOfEmployees
FROM dbo.Department
ORDER BY Department
GO
/* Results
Department NoOfEmployees
Document Control 5
Engineering 6
Executive 2
Facilities and Maintenance 7
Finance 10
Human Resources 6
Information Services 10
Marketing 9
Production 179
Production Control 6
Purchasing 12
Quality Assurance 6
Research and Development 4
Sales 18
Shipping and Receiving 6
Tool Design 4
*/
The following example demonstrates UPDATE with CTE. We give a 20% boost in vacation hours to members of the Engineering department. Recursive CTE is used to identify engineering staff. It starts with the Vice President of Engineering as root (anchor). Full table aliasing used to make the UPDATE query understandable and maintainable.
USE AdventureWorks;
GO
SELECT Title FROM HumanResources.Employee
WHERE EmployeeID in (SELECT distinct ManagerID from HumanResources.Employee)
AND Title like 'Vice President %';
/* Results
Vice President of Engineering
Vice President of Production
Vice President of Sales
*/
-- SQL recursive cte - enumerate all staff in engineering
-- SQL self-join - on tables e and e1 in recursion anchor member
WITH cteEngineeringStaff(EmployeeID, OrganizationalLevel)
AS
(SELECT e.EmployeeID, 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.Employee AS e1
ON e.ManagerID = e1.EmployeeID
WHERE e1.Title = 'Vice President of Engineering'
UNION ALL
SELECT e.EmployeeID, OrganizationalLevel + 1
FROM HumanResources.Employee as e
INNER JOIN cteEngineeringStaff AS d
ON e.ManagerID = d.EmployeeID
)
UPDATE e
SET e.VacationHours = e.VacationHours * 1.20
FROM HumanResources.Employee AS e
INNER JOIN cteEngineeringStaff AS es
ON e.EmployeeID = es.EmployeeID;
GO
-- (13 row(s) affected)
The following example demonstrates UPDATE with multiple value assignments in one SET statement. While it is OK to use it for test set generation, there is an ongoing debate in SQL Server circles about if it can be used for RUNNING TOTAL and similar calculations, where the order of UPDATE execution matters.
USE tempdb;
-- SQL select into create table - SQL newid - random sorting
SELECT TOP 500 *
INTO POH
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY NEWID()
GO
-- Remap order dates to recent dates from pre-2005
-- SQL multiple value assignment
DECLARE @OrderDate datetime
SET @OrderDate = getdate()
UPDATE POH
SET @OrderDate = OrderDate = dateadd(day,-1,@OrderDate)
GO
SELECT TOP (5) PurchaseOrderID, VendorID,OrderDate
FROM POH
GO
/* Results
PurchaseOrderID VendorID OrderDate
1394 62 2012-01-09 09:16:18.367
607 93 2012-01-08 09:16:18.367
3298 57 2012-01-07 09:16:18.367
2972 94 2012-01-06 09:16:18.367
315 91 2012-01-05 09:16:18.367
*/
-- SQL drop table
DROP TABLE tempdb.dbo.POH
GO
----------
The following complex example demonstrates CTE UPDATE whereby updating the CTE updates the underlying table.
------------
-- UPDATE using CTE - CTE UPDATE
------------
USE tempdb;
SELECT * INTO Product FROM AdventureWorks2008.Production.Product
GO
-- (504 row(s) affected)
SELECT TOP (3) ProductID, ListPrice FROM Product
WHERE Color is not null and ListPrice > 0
/* ProductID ListPrice
680 1431.50
706 1431.50
707 34.99 */
;WITH CTE AS
(SELECT * FROM Product
WHERE Color is not null and ListPrice > 0.0)
UPDATE CTE
SET ListPrice= ListPrice * 1.1;
GO
-- (245 row(s) affected)
/* ProductID ListPrice
680 1574.65
706 1574.65
707 38.489 */
DROP TABLE tempdb.dbo.Product
------------
First we create a new table for experimentation from the AdventureWorks database and perform a demo inner join UPDATE on the new table.
USE tempdb;
SELECT ProductID,
ProductName = Name,
COST = StandardCost,
ListPrice,
Color,
ModifiedDate
INTO Product
FROM AdventureWorks2008.Production.Product
WHERE ListPrice > 0.0
AND Color IS NOT NULL
GO
-- (245 row(s) affected)
-- UPDATE with INNER JOIN - QUICK SYNTAX
UPDATE p
SET p.ModifiedDate = DATEADD(dd,1,awp.ModifiedDate)
FROM Product p
INNER JOIN AdventureWorks2008.Production.Product awp
ON p.ProductID = awp.ProductID
WHERE p.Color = 'Yellow'
-- (36 row(s) affected)
SELECT TOP (5) * FROM Product ORDER BY ProductID
GO
/* Partial Results
ProductID ProductName Cost ListPrice Color
680 HL Road Frame - Black, 58 1059.31 1431.50 Black
706 HL Road Frame - Red, 58 1059.31 1431.50 Red
707 Sport-100 Helmet, Red 13.0863 34.99 Red
708 Sport-100 Helmet, Black 13.0863 34.99 Black
709 Mountain Bike Socks, M 3.3963 9.50 White
*/
We shall proceed and update ALL the rows in the Product table. We increase the ListPrice by 5%.
UPDATE Product
SET ListPrice = ListPrice * 1.05
GO
SELECT TOP (5) * FROM Product ORDER BY ProductID
GO
/* Results
ProductID ProductName Cost ListPrice Color
680 HL Road Frame - Black, 58 1059.31 1503.075 Black
706 HL Road Frame - Red, 58 1059.31 1503.075 Red
707 Sport-100 Helmet, Red 13.0863 36.7395 Red
708 Sport-100 Helmet, Black 13.0863 36.7395 Black
709 Mountain Bike Socks, M 3.3963 9.975 White
*/
If we look in messages window we see (245 row(s) affected) for the UPDATE statement. Those are all the rows in the Product table.
The biggest danger with the UPDATE statement is that without proper FILTERING all the rows in the table are updated. UPDATE is as destructive as DELETE if happens unintentionally.
The following UPDATE statement applies a WHERE filter for a single row UPDATE of 10% increase in the ListPrice.
/* ALL rows updated to 599 due to lack of WHERE filtering
UPDATE Product
SET ListPrice = 599.00
*/
UPDATE Product
SET ListPrice = ListPrice * 1.10
WHERE ProductID = 680
GO
-- Messages: (1 row(s) affected)
SELECT TOP (5) * FROM Product ORDER BY ProductID
GO
/* Results
ProductID ProductName Cost ListPrice Color
680 HL Road Frame - Black, 58 1059.31 1653.3825 Black
706 HL Road Frame - Red, 58 1059.31 1503.075 Red
707 Sport-100 Helmet, Red 13.0863 36.7395 Red
708 Sport-100 Helmet, Black 13.0863 36.7395 Black
709 Mountain Bike Socks, M 3.3963 9.975 White
*/
-- SQL UPDATE CTE - UPDATE underlying table through CTE
;WITH CTE
AS (SELECT Price = ListPrice
FROM Product
WHERE ListPrice > 1000.0)
UPDATE CTE
SET Price = Price * 1.05
GO
-- (86 row(s) affected)
-- SQL ANSI Standard UPDATE
UPDATE Product
SET ListPrice = (SELECT p8.ListPrice * 1.25
FROM AdventureWorks2008.Production.Product p8
WHERE Product.ProductID = p8.ProductID)
WHERE EXISTS (SELECT *
FROM AdventureWorks2008.Production.Product p8
WHERE Product.ProductID = p8.ProductID
AND Product.ListPrice > 1000.0);
-- (86 row(s) affected)
The confusion factor: the following two statements are equivalent to each other. The second statement is really perplexing. Why does it work with two references to the same table without a SELF-JOIN?
UPDATE Product
SET ListPrice = ListPrice * 1.10
WHERE ProductID = 680
GO
-- Messages: (1 row(s) affected)
UPDATE Product
SET ListPrice = ListPrice * 1.10
FROM Product
WHERE ProductID = 680
GO
-- Messages: (1 row(s) affected)
To take the confusion factor out from the UPDATE statement:
1. We have to alias the table to be updated in the FROM clause
2. We have to use the use the table alias after the UPDATE
NOTE: only the FROM table(s) can be aliased. Example:
Aliasing attempt after UPDATE gives an error:
UPDATE Product p
SET ListPrice = ListPrice * 1.10
FROM p
WHERE ProductID = 680
GO
/*
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'p'.
*/
Correct syntax with aliasing:
UPDATE p SET ListPrice = ListPrice * 1.10
FROM Product p
WHERE ProductID = 680
GO
-- Messages: (1 row(s) affected)
It is clear that we are updating the single-referenced Product table. The table aliasing solution becomes real important in complex updates when multiple tables are involved.
If you want to be confident about an UPDATE query, you must use aliases after UPDATE and after SET. The following complex query demonstrate how aliasing removes any doubt of what is going to happen in the UPDATE query.
USE tempdb;
SELECT * INTO SalesPerson from AdventureWorks.Sales.SalesPerson
SELECT * INTO SalesOrderHeader from AdventureWorks.Sales.SalesOrderHeader
SELECT * INTO SalesPerson from AdventureWorks.Sales.SalesPerson
GO
-- UPDATE all rows
UPDATE SalesPerson SET SalesYTD = 0.0
GO
-- (17 row(s) affected)
SELECT TOP (5) SalesPersonID, SalesYTD FROM SalesPerson ORDER BY SalesPersonID
/* Partial results
SalesPersonID SalesYTD
268 0.00
275 0.00
276 0.00
277 0.00
278 0.00
*/
UPDATE sp
SET sp.SalesYTD = sp.SalesYTD + sod.SubTotal
FROM SalesPerson sp
JOIN SalesOrderHeader sod
ON sp.SalesPersonID = sod.SalesPersonID
AND sod.OrderDate = (SELECT MAX(OrderDate)
FROM SalesOrderHeader
WHERE SalesPersonID = sp.SalesPersonID);
GO
-- (17 row(s) affected)
SELECT TOP (5) SalesPersonID, SalesYTD FROM SalesPerson ORDER BY SalesPersonID
/* Partial results
SalesPersonID SalesYTD
268 35331.66
275 32700.6053
276 713.796
277 30163.638
278 +27995.85
*/
(17 row(s) affected)
The following Microsoft SQL Server 2008 T-SQL UPDATE syntax is using the new MERGE statement.
---------------
-- SQL UPDATE applying MERGE
--------------
USE tempdb;
SELECT * INTO Product FROM AdventureWorks2008.Production.Product
GO
-- (504 row(s) affected)
SELECT TOP (3) ProductID, ListPrice FROM Product
WHERE Color is not null and ListPrice > 0
/* ProductID ListPrice
680 1431.50
706 1431.50
707 34.99 */
MERGE INTO tempdb.dbo.Product AS Target
USING (SELECT ProductID, ListPrice
FROM tempdb.dbo.Product
WHERE Color is not null AND ListPrice > 0.0)
AS Source (ProductID, ListPrice)
ON Target.ProductID = Source.ProductID
WHEN MATCHED THEN
UPDATE SET ListPrice= Source.ListPrice * 1.1;
-- (245 row(s) affected)
/* ProductID ListPrice
680 1574.65
706 1574.65
707 38.489 */
--------------
The following example demonstrates UPDATE from a different database. Both tables aliased in the FROM clause.
USE CopyOfAdventureWorks;
SELECT IsOnlyStateProvinceFlag FROM Person.StateProvince
WHERE Name='Virgin Islands';
GO
-- 1
-- SQL update - one table only, set flag to 0
UPDATE Person.StateProvince
SET IsOnlyStateProvinceFlag=0
WHERE Name='Virgin Islands';
GO
-- (1 row(s) affected)
SELECT IsOnlyStateProvinceFlag FROM Person.StateProvince
WHERE Name='Virgin Islands';
GO
-- 0
-- SQL update, two tables, one of the tables in a different database
-- SQL 3-part reference
UPDATE sp
SET sp.IsOnlyStateProvinceFlag = awsp.IsOnlyStateProvinceFlag
FROM Person.StateProvince sp
INNER JOIN AdventureWorks.Person.StateProvince awsp
ON sp.StateProvinceID = awsp.StateProvinceID
WHERE sp.Name='Virgin Islands';
GO
-- (1 row(s) affected)
SELECT IsOnlyStateProvinceFlag FROM Person.StateProvince
WHERE Name='Virgin Islands';
GO
-- 1
The following complex example demonstrates UPDATE with an INNER JOIN to SELECT - GROUP BY subquery (derived table DG) .
USE tempdb
GO
SELECT * into dbo.Department
FROM AdventureWorks.HumanResources.Department
ALTER TABLE dbo.Department
ADD NoOfEmployees int not null default (0)
GO
UPDATE DE
SET DE.NoOfEmployees =DG.DeptEmployees,
DE.ModifiedDate = CURRENT_TIMESTAMP
FROM dbo.Department DE
JOIN (SELECT D.DepartmentID, COUNT(*) AS DeptEmployees
FROM AdventureWorks.HumanResources.EmployeeDepartmentHistory EDH
JOIN dbo.Department D
ON d.DepartmentID = EDH.DepartmentID
and EDH.EndDate is null
JOIN AdventureWorks.HumanResources.Employee E
ON EDH.EmployeeID = E.EmployeeID
GROUP BY D.DepartmentID ) DG
ON DE.DepartmentID = DG.DepartmentID
GO
-- (16 row(s) affected)
SELECT Department=Name, NoOfEmployees
FROM dbo.Department
ORDER BY Department
GO
/* Results
Department NoOfEmployees
Document Control 5
Engineering 6
Executive 2
Facilities and Maintenance 7
Finance 10
Human Resources 6
Information Services 10
Marketing 9
Production 179
Production Control 6
Purchasing 12
Quality Assurance 6
Research and Development 4
Sales 18
Shipping and Receiving 6
Tool Design 4
*/
The following example demonstrates UPDATE with CTE. We give a 20% boost in vacation hours to members of the Engineering department. Recursive CTE is used to identify engineering staff. It starts with the Vice President of Engineering as root (anchor). Full table aliasing used to make the UPDATE query understandable and maintainable.
USE AdventureWorks;
GO
SELECT Title FROM HumanResources.Employee
WHERE EmployeeID in (SELECT distinct ManagerID from HumanResources.Employee)
AND Title like 'Vice President %';
/* Results
Vice President of Engineering
Vice President of Production
Vice President of Sales
*/
-- SQL recursive cte - enumerate all staff in engineering
-- SQL self-join - on tables e and e1 in recursion anchor member
WITH cteEngineeringStaff(EmployeeID, OrganizationalLevel)
AS
(SELECT e.EmployeeID, 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.Employee AS e1
ON e.ManagerID = e1.EmployeeID
WHERE e1.Title = 'Vice President of Engineering'
UNION ALL
SELECT e.EmployeeID, OrganizationalLevel + 1
FROM HumanResources.Employee as e
INNER JOIN cteEngineeringStaff AS d
ON e.ManagerID = d.EmployeeID
)
UPDATE e
SET e.VacationHours = e.VacationHours * 1.20
FROM HumanResources.Employee AS e
INNER JOIN cteEngineeringStaff AS es
ON e.EmployeeID = es.EmployeeID;
GO
-- (13 row(s) affected)
The following example demonstrates UPDATE with multiple value assignments in one SET statement. While it is OK to use it for test set generation, there is an ongoing debate in SQL Server circles about if it can be used for RUNNING TOTAL and similar calculations, where the order of UPDATE execution matters.
USE tempdb;
-- SQL select into create table - SQL newid - random sorting
SELECT TOP 500 *
INTO POH
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY NEWID()
GO
-- Remap order dates to recent dates from pre-2005
-- SQL multiple value assignment
DECLARE @OrderDate datetime
SET @OrderDate = getdate()
UPDATE POH
SET @OrderDate = OrderDate = dateadd(day,-1,@OrderDate)
GO
SELECT TOP (5) PurchaseOrderID, VendorID,OrderDate
FROM POH
GO
/* Results
PurchaseOrderID VendorID OrderDate
1394 62 2012-01-09 09:16:18.367
607 93 2012-01-08 09:16:18.367
3298 57 2012-01-07 09:16:18.367
2972 94 2012-01-06 09:16:18.367
315 91 2012-01-05 09:16:18.367
*/
-- SQL drop table
DROP TABLE tempdb.dbo.POH
GO
----------
The following complex example demonstrates CTE UPDATE whereby updating the CTE updates the underlying table.
------------
-- UPDATE using CTE - CTE UPDATE
------------
USE tempdb;
SELECT * INTO Product FROM AdventureWorks2008.Production.Product
GO
-- (504 row(s) affected)
SELECT TOP (3) ProductID, ListPrice FROM Product
WHERE Color is not null and ListPrice > 0
/* ProductID ListPrice
680 1431.50
706 1431.50
707 34.99 */
;WITH CTE AS
(SELECT * FROM Product
WHERE Color is not null and ListPrice > 0.0)
UPDATE CTE
SET ListPrice= ListPrice * 1.1;
GO
-- (245 row(s) affected)
/* ProductID ListPrice
680 1574.65
706 1574.65
707 38.489 */
DROP TABLE tempdb.dbo.Product
------------
No comments:
Post a Comment