13. UPDATE: changing rows

UPDATE changes values in rows that already exist. The shape is: name the table, SET the new values, and — crucially — WHERE to say which rows.

These playgrounds start with a small seeded product table:

CREATE TABLE product (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR NOT NULL,
  price    DECIMAL(6,2) NOT NULL,
  in_stock BOOLEAN NOT NULL DEFAULT true
);
INSERT INTO product (id, name, price, in_stock) VALUES
  (1, 'Notebook', 4.50, true),
  (2, 'Pen',      1.25, true),
  (3, 'Backpack', 39.00, false),
  (4, 'Mug',      8.00, true);

Update specific rows

UPDATE product
SET price = 5.00
WHERE id = 1;

SELECT * FROM product ORDER BY id;

The WHERE clause is not optional (in practice)

Leave off WHERE and you update every row:

UPDATE product
SET in_stock = false;      -- no WHERE → the whole table changes

SELECT * FROM product ORDER BY id;

That's occasionally what you want, but far more often it's a costly mistake. Make WHERE a reflex, and it never bites you.

Update using the current value

The new value can be an expression, including the column's own value — here, a 10% price rise for everything under $10:

UPDATE product
SET price = price * 1.10
WHERE price < 10.00;

SELECT * FROM product ORDER BY id;

You can set several columns at once: SET price = 9.99, in_stock = true.

Try it

Mark the 'Backpack' as in stock (in_stock = true) and drop its price to 35.00 in a single UPDATE, then select all rows ordered by id.

-- update the Backpack row, then select all ordered by id

UPDATE product
SET in_stock = true, price = 35.00
WHERE name = 'Backpack';
SELECT * FROM product ORDER BY id;