Level 3 · Plan a price rise with UPDATE

UPDATE changes values in rows that already exist. It is the most dangerous statement an analyst types, because it has no undo once committed, and one missing line changes every row in the table.

UPDATE … SET … WHERE

UPDATE price_plan
SET new_price = ROUND(old_price * 1.06, 2)
WHERE subcategory IN ('Pendants', 'Sconces');

SET says which columns get which new values. The expression can use the row’s current values, here old_price. WHERE decides which rows change, using the same conditions you already know from SELECT. Every row that matches is changed; every other row is left alone. You can set several columns at once: SET a = ..., b = ....

Preview first, then update a copy

Two habits keep an UPDATE safe.

Run the WHERE as a SELECT first. Swap UPDATE ... SET ... for SELECT * FROM ... with the same WHERE and look at what comes back. On the active Lighting catalog it returns 10 rows, 5 Pendants and 5 Sconces. If it had returned 20, you would know the condition was wrong before anything changed.

Work on a copy. The starter builds price_plan with CREATE TABLE ... AS SELECT, keeping the current price twice: old_price stays as it is and new_price gets updated. The plan can be reviewed, changed and thrown away without touching products, and the comparison is one GROUP BY:

subcategory	products	changed_products	avg_old_price	avg_new_price
Floor Lamps	5	0	138.20	138.20
Pendants	5	5	183.40	194.40
Sconces	5	5	216.20	229.17
Table Lamps	5	0	222.00	222.00

The two untouched subcategories are the proof that the WHERE did its job.

On a real database, wrap risky changes in a transaction: BEGIN TRANSACTION;, the UPDATE, a checking SELECT, then COMMIT; if it looks right or ROLLBACK; if not. This game does the rollback for you after every run, which is why each run starts by recreating price_plan.

Common mistakes

• No WHERE. UPDATE price_plan SET new_price = ... changes all 20 rows. Most SQL clients will run it without a warning.
• Updating the source table. Raising products.unit_price directly destroys the old price, so the comparison is impossible and every report reading products changes at once.
• Rounding at the wrong time. Round each new price, as a shop would, then average. Averaging unrounded prices can differ by a cent.
• Comparing with NULL. new_price <> old_price is unknown when either side is NULL; on this copy every Lighting product has a price, but check before you count changed rows on other data.

In SQL Server

UPDATE ... SET ... WHERE is identical in T-SQL. SQL Server adds an OUTPUT clause that returns the before and after values of every changed row, handy as a receipt:

BEGIN TRAN;
UPDATE dbo.PricePlan SET NewPrice = ROUND(OldPrice * 1.06, 2)
OUTPUT deleted.NewPrice, inserted.NewPrice
WHERE Subcategory IN ('Pendants', 'Sconces');
ROLLBACK;  -- or COMMIT

In SSMS, BEGIN TRAN stays open until you commit or roll back, so finish it.