UPDATE: changing rows in SQL

Problem

A price rise

The dairy supplier raises prices by 10%. From today the shop has to sell everything in the “Dairy” category 10% higher as well.

There are five products. You could change the prices by hand, one by one. But tomorrow it will be a category with 200 products, and copying by hand is sure to go wrong.

The idea

UPDATE … SET … WHERE

UPDATE says which table, SET says which column and what it becomes, and WHERE says in which rows:

SQLdatabase: shop

UPDATE products
SET price = ROUND(price * 1.1, 2)
WHERE category_id = 4;

SELECT name, price FROM products WHERE category_id = 4;

price * 1.1 is worked out for each row separately, with that row's price. Fresh milk was 1.60 € and is now 1.76 €. ROUND(…, 2) rounds to the cent, because the price is in euros and cents. Above the result you can see how many rows the statement changed: five.

More precisely, if you are curious

Without ROUND the screen also shows 1.76. But for the computer 1.6 × 1.1 is 1.7600000000000002, because fractions are stored in binary and not always exactly. Then a search for the price 1.76 does not find the fresh milk:

SQLdatabase: shop

UPDATE products SET price = price * 1.1 WHERE id = 5;

SELECT name, price FROM products WHERE price = 1.76;

So round money when you store it. Real shops often keep prices in whole cents (176) or in a column with an exact decimal type, which PostgreSQL and MySQL have (DECIMAL). SQLite has no such type.

SET can hold several columns, separated by commas. 30 banitsas arrive, and their price becomes 2.60 €:

SQLdatabase: shop

UPDATE products
SET stock = stock + 30, price = 2.6
WHERE id = 4;

SELECT name, price, stock FROM products WHERE id = 4;

stock + 30 takes the old stock and adds 30. In the expression to the right of =, the columns hold the old values of the row.

Try it

An order shipped

Order 59 is paid and went out with the courier today. Change its status to shipped.

SQLdatabase: shop

SELECT id, customer_id, ordered_at, status FROM orders WHERE id = 59;

Solution

UPDATE orders SET status = 'shipped' WHERE id = 59;

See it

UPDATE without WHERE

The manager wants a promotion: mint tea for 0.99 €. Someone types in a hurry and forgets WHERE:

SQLdatabase: shop

UPDATE products SET price = 0.99;

SELECT name, price FROM products;

All 25 products now cost 0.99 €, even the knife set that was 39 €. The database does not ask “are you sure?”. Without WHERE the statement is for every row.

Here the “Fresh database” button fixes everything. A real database has no such button. What is left are backups and transactions: with a transaction a change can be undone before it is confirmed. You will meet them later in the course.

The idea

SELECT first

The habit that protects you from this: before an UPDATE, run a SELECT with the same WHERE. You see exactly which rows will change, and nothing changes.

Drinks go up by 5%. The category is known by name, and a subquery gives its number:

SQLdatabase: shop

SELECT id, name, price FROM products
WHERE category_id = (SELECT id FROM categories WHERE name = 'Drinks');

Four products, exactly the right ones. Now SELECT … FROM becomes UPDATE … SET, and WHERE stays the same:

SQLdatabase: shop

UPDATE products
SET price = ROUND(price * 1.05, 2)
WHERE category_id = (SELECT id FROM categories WHERE name = 'Drinks');

SELECT id, name, price FROM products
WHERE category_id = (SELECT id FROM categories WHERE name = 'Drinks');

Here ROUND makes a visible difference: 0.90 × 1.05 is 0.945, and 14.90 × 1.05 is 15.645. The prices become 0.95 € and 15.65 €.

A subquery in WHERE works in UPDATE just as it does in SELECT.

Try it

The forgotten orders

There are orders with the status new from before 2026 that nobody has paid. Cancel them: their status becomes cancelled.

SQLdatabase: shop

SELECT id, customer_id, ordered_at, status FROM orders WHERE status = 'new';

Solution

UPDATE orders SET status = 'cancelled'
WHERE status = 'new' AND ordered_at < '2026-01-01';

Exercise

Two tasks

1. A promotion for the products the shop has plenty of: every product with more than 50 in stock gets 20% cheaper, rounded to the cent. The gift card has no category and is not part of the promotion.

The promotion (the whole products table is checked):

SQLdatabase: shop

SELECT id, name, category_id, price, stock FROM products WHERE stock > 50;

Solution

UPDATE products
SET price = ROUND(price * 0.8, 2)
WHERE stock > 50 AND category_id IS NOT NULL;

2. Maria Ivanova (maria.ivanova@example.com) paid all her new orders at once. Change their status to paid. Find the customer by email, with a subquery.

Maria's new orders become paid:

SQLdatabase: shop

Solution

UPDATE orders SET status = 'paid'
WHERE status = 'new'
  AND customer_id = (SELECT id FROM customers WHERE email = 'maria.ivanova@example.com');

Test

Check yourself

1. What does UPDATE products SET stock = 0; without WHERE do?It sets the stock of every product to zeroIt sets the stock of the first product to zeroAn error: WHERE is missing

2. How do you add 30 to the stock of product 4?SET stock = stock + 30SET stock = 30SET stock + 30

3. What is the good habit before an UPDATE?Run the UPDATE twice to be safeRun a SELECT with the same WHERE and see which rows will changeOnly ever write UPDATE with WHERE id = …

4. What changes with UPDATE orders SET status = 'paid', ordered_at = '2026-10-25' WHERE id = 34;?status of every order and ordered_at of order 34Only statusstatus and ordered_at of order 34

UPDATE: changing rows

Cookies