Changing and deleting rows

Introduction

UPDATE table SET column = value, other = value WHERE condition;
DELETE FROM table WHERE condition;

UPDATE changes values in the rows that match; DELETE removes the rows that match. The WHERE works exactly as in SELECT.

Step through and watch the Tables panel: changed values are marked, and removed rows are crossed out for one step.

• The new value can be a calculation from the old one: price = price * 1.10 raises every matching price by 10%. ROUND(..., 2) keeps 2 decimal places, because REAL numbers are stored in binary and are not exact: 900 * 1.10 comes out as 990.0000000000001. For money, other DBMSs have an exact type, DECIMAL(10, 2).
• SET can change several columns at once, separated by commas.
• When no row matches, nothing changes, and that is not an error. The explanation says so.

Data does not stay still: prices rise, stock runs out and records become obsolete. UPDATE and DELETE make those changes, and they deserve care because they cannot be undone by themselves.

How it works

The full worked example is in the Example section below. Before it, here is the mistake every database user meets once.

Forget the WHERE, change everything

CREATE TABLE results (
  name TEXT,
  marks INTEGER
);
INSERT INTO results VALUES ('Rahim', 85), ('Karim', 72), ('Nadia', 91);

-- Meant: give Karim 0 for a missed exam. Forgot the WHERE:
UPDATE results SET marks = 0;

SELECT * FROM results;

Without WHERE, UPDATE changes every row and DELETE removes every row. Nothing asks "are you sure?", and there is no undo. This is one of the most common serious mistakes in real systems.

Two safe habits:

1. Write the SELECT first. Run SELECT * FROM products WHERE stock = 0;, check that those are exactly the rows you mean, then change SELECT * to DELETE.
2. Use a transaction (BEGIN, then COMMIT or ROLLBACK), so a mistake can still be undone. A later lesson shows how.

DELETE FROM t; empties the table but keeps it; DROP TABLE t; removes the table itself.

Syntax

UPDATE products SET price = price + 5, stock = stock - 1 WHERE id = 3;
DELETE FROM products WHERE stock = 0;

Example

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price REAL,
  stock INTEGER
);
INSERT INTO products (name, price, stock) VALUES
  ('Pen', 10, 120), ('Notebook', 60, 40), ('Bag', 900, 0), ('Ruler', 25, 0);

UPDATE products SET stock = 35 WHERE name = 'Bag';

-- Everything that costs 50 or more gets 10% dearer, and 10 more in stock
UPDATE products SET price = ROUND(price * 1.10, 2), stock = stock + 10 WHERE price >= 50;

-- No product is called Eraser: nothing changes
UPDATE products SET price = 5 WHERE name = 'Eraser';

DELETE FROM products WHERE stock = 0;

SELECT * FROM products;

The example makes four changes to a small table of products and then shows the result.

Line by line

1. The first UPDATE sets the Bag's stock to 35, and touches no other row.
2. The second UPDATE matches the products that cost 50 or more. It raises each price by 10% with ROUND(price * 1.10, 2), and adds 10 to its stock.
3. The third UPDATE looks for an Eraser. There is none, so nothing changes, and there is no error.
4. DELETE FROM products WHERE stock = 0; removes the Ruler, the only product still out of stock.
5. SELECT * FROM products; shows the three products left, with their new prices and stock.

Expected result

id  name      price  stock
--  --------  -----  -----
1   Pen       10.0   120
2   Notebook  66.0   50
3   Bag       990.0  45
(3 rows)

In the real world

Every "edit" and "remove" button in an application runs an UPDATE or DELETE. Because a missing WHERE is such a costly mistake, many companies require a second person to check any change run by hand on a live database.

Common mistakes

• Forgetting the WHERE, which changes or deletes every row.
• Writing the WHERE for the wrong rows, without checking it with a SELECT first.
• Expecting decimal arithmetic on REAL columns to be exact, instead of rounding.

Best practices

• Run the SELECT with the same WHERE first, and check the rows.
• Work inside a transaction when changing important data by hand.
• Use the primary key in the WHERE when you mean exactly one row.

Summary

UPDATE changes values in matching rows, computing new values from old ones if needed, and DELETE removes matching rows. Without WHERE they affect every row, so check with a SELECT first.

Mark lesson complete