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