Updates with CTE

Performing updates on columns based on values from another table is a very common need. Using the ANSI UPDATE normally requires multiple subqueries, which can be very inefficient especially if multiple filters have to be applied. The Microsoft specific UPDATE with JOIN is one solution. However, common table expressions provide a very elegant alternative, which has the same efficient plan as UPDATE with JOIN, but is much easier to read and maintain. The sample below demonstrates how to perform update based on another table using join and CTE. Using MERGE in SQL Server 2008 will make it even better.

CREATE TABLE Products (

 sku CHAR(5) NOT NULL PRIMARY KEY,

 product_desc VARCHAR(35) NOT NULL,

 price DECIMAL(12, 2) NOT NULL DEFAULT 0);

 

CREATE TABLE ProductUpdates (

 sku CHAR(5) NOT NULL PRIMARY KEY,

 product_desc VARCHAR(35) NOT NULL,

 price DECIMAL(12, 2) NOT NULL DEFAULT 0,

 effective_date DATETIME NOT NULL);

 

INSERT INTO Products VALUES ('CHS01', 'Child seat', 25.50);

INSERT INTO Products VALUES ('CUP03', 'Water cup', 5.25);

INSERT INTO Products VALUES ('HOL01', 'Cup holder', 3.50);

 

INSERT INTO ProductUpdates VALUES ('CHS01', 'Child seat with cushion', 26.95, '20080301');

INSERT INTO ProductUpdates VALUES ('CUP03', 'Water cup with handle', 6.25, '20080405');

 

-- Update all current product descriptions and prices

-- with updates that have effective date past today (March 12, 2008)

WITH Updates

AS

(SELECT P.product_desc,

        P.price,

        U.product_desc AS new_product_desc,

        U.price AS new_price

 FROM Products AS P

 JOIN ProductUpdates AS U

   ON P.sku = U.sku

 WHERE U.effective_date < CURRENT_TIMESTAMP)

UPDATE Updates

SET product_desc = new_product_desc,

    price = new_price;

 

SELECT sku, product_desc, price

FROM Products;

 

-- Results

sku   product_desc            price

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

CHS01 Child seat with cushion 26.95

CUP03 Water cup                5.25

HOL01 Cup holder               3.50       

Labels: ,