datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS

 

Mastering the SQL UPDATE Statement

By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, MCDBA, MCITP

January 2, 2011

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

INNER 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

INNER JOIN (SELECT D.DepartmentID, COUNT(*) AS DeptEmployees

      FROM AdventureWorks.HumanResources.EmployeeDepartmentHistory EDH

      INNER JOIN dbo.Department D

            ON d.DepartmentID = EDH.DepartmentID

          and EDH.EndDate is null

      INNER 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

------------

Related articles:

UPDATE (Transact-SQL)

UPDATE Basics in SQL Server

 

Exam Prep 70-461
Exam 70-461