Updating Data
Change existing rows with UPDATE ... SET, always limited by a WHERE that you've tested first with a SELECT.
Apprentice · T-SQL · SQL · Updated
After this lesson you can
- Change existing rows with UPDATE and SET, limited by a WHERE clause
Key takeaways
SETlists each column to change and its new value;WHEREsays which rows change. Without aWHERE, every row in the table changes.- Before every
UPDATE, run aSELECTwith the sameWHEREand check that it returns exactly the rows you mean to change. - The new value can use the row's current value, as in
SET UnitPrice = UnitPrice * 1.10. - The rows-affected count should match the rows your
SELECTreturned. If it doesn't, stop and find out why.
UPDATE changes values in rows that already exist.
UPDATE <table>
SET <column1> = <value1>
, <column2> = <value2>
WHERE <which rows>;
SETlists each column to change and its new value.WHEREsays which rows change. Without it, every row in the table changes.
One column of one row
UPDATE Customers
SET ContactName = 'Alfred Blauer'
WHERE CustomerId = 'ALFKI';
(1 row affected)
Filtering on the primary key is the safest WHERE there is: it matches exactly one row.
Several columns at once
UPDATE Products
SET UnitPrice = 19.99
, UnitsInStock = 25
WHERE ProductId = 5;
Calculating the new value
The new value can use the row's current values. This raises every beverage's price by 10%:
UPDATE Products
SET UnitPrice = UnitPrice * 1.10
WHERE CategoryId = 1;
(12 rows affected)
For each matching row, UnitPrice on the right is the row's price before the change.
Forgetting the WHERE
Don't run this one; just read it.
UPDATE Orders
SET ShippedDate = '2026-04-01';
This sets the shipped date of all 830 orders. There's no undo button, and in a real company's database that's a very bad day. SSMS won't warn you.
Always SELECT first
Before every UPDATE, run a SELECT with the same WHERE, and check that it returns exactly the rows you mean to change:
SELECT ProductId, ProductName, UnitPrice
FROM Products
WHERE ProductId = 15;
When the rows are right, change SELECT ... FROM to UPDATE ... SET, and keep the WHERE exactly as it was:
UPDATE Products
SET UnitPrice = UnitPrice * 1.10
WHERE ProductId = 15;
The (1 row affected) message should match the number of rows your SELECT returned. If it doesn't, stop and find out why. The next lesson in this chapter shows how a transaction gives you a way back.
Do it: raise the Condiments prices by $1.50
The company that makes Northwind's sauces and spreads has raised its prices, so every product in the Condiments category now costs $1.50 more. Products in other categories keep their prices.
The Products table (the Tables tab shows the SQL that makes it) has four columns:
ProductId: the product's number.ProductName: the product's name.CategoryId: the product's category. Condiments is category2.UnitPrice: the price of one.
It has these rows. The three Condiments are the ones with CategoryId 2:
| ProductId | ProductName | CategoryId | UnitPrice | After your UPDATE |
|---|---|---|---|---|
| 1 | Chai | 1 | 18.00 | 18.00 (not Condiments: no change) |
| 2 | Chang | 1 | 19.00 | 19.00 (no change) |
| 3 | Aniseed Syrup | 2 | 10.00 | 10.00 + 1.50 = 11.50 |
| 4 | Chef Anton's Cajun Seasoning | 2 | 22.00 | 22.00 + 1.50 = 23.50 |
| 6 | Grandma's Boysenberry Spread | 2 | 25.00 | 25.00 + 1.50 = 26.50 |
| 11 | Queso Cabrales | 4 | 21.00 | 21.00 (no change) |
After your SQL runs, the check reads ProductId, ProductName and UnitPrice of every product, sorted by ProductId, and it should give exactly the prices in the last column.
Your job: write one UPDATE on Products.
- Add 1.50 to the
UnitPriceof each product whoseCategoryIdis2. - Work the new price out from the row's current price, so the same statement works for every Condiments product.
- Every product in another category keeps its price.
Practiced live on the lesson page: write the SQL right there and check it.
Check yourself
2 questions to try on the lesson page.
So what
UPDATE is only as safe as its WHERE. Test the WHERE with a SELECT, change only what you meant to change, and check the row count every time.