Changing data: INSERT, UPDATE, DELETE

INSERT

Name the columns, then give a value for each. The columns you leave out get their default — added_at here defaults to the current timestamp.

INSERT INTO wishlists (customer_id, product_id)
VALUES (1, 288);

One row was inserted. Naming columns explicitly avoids relying on the table's column order, so the statement remains valid when columns are added later.

What the database refuses

Run exactly the same statement again.

-- The same statement a second time. (customer_id, product_id) is the
-- primary key of wishlists, so the database refuses the duplicate.
INSERT INTO wishlists (customer_id, product_id)
VALUES (1, 288);

The statement raises an error and changes nothing. wishlists has PRIMARY KEY (customer_id, product_id), so the same pair cannot exist twice. The constraint prevents invalid data from being stored.

UPDATE, and the clause that matters

UPDATE customers
SET loyalty_points = loyalty_points + 100
WHERE id = 1;

Before changing data, run a SELECT with the same FROM and WHERE clauses to inspect the affected rows.

Planche can do this automatically. With Settings › Safety › Preview affected rows enabled, an UPDATE or DELETE is rewritten as a matching SELECT, counted, and shown before it runs. Read-only connections and per-connection production colors are configured in the same place.

SELECT id, first_name, last_name, loyalty_points
FROM customers
WHERE id = 1;

DELETE

DELETE FROM wishlists
WHERE customer_id = 1
  AND product_id = 288;

Choosing the rows with a subquery

The WHERE clause of an UPDATE or DELETE is an ordinary WHERE clause, so everything from the subqueries lesson works here. A subquery is how you change rows in one table based on what is in another.

-- Which rows to change is its own question, and a subquery can answer it.
UPDATE customers
SET loyalty_points = loyalty_points + 50
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'refunded'
    AND total > 2000
);

242 customers have a refunded order over 2,000, so 242 rows change. As before, run SELECT id FROM customers WHERE id IN (...) first and check the count before applying the UPDATE.

DELETE FROM wishlists
WHERE product_id IN (
  SELECT id FROM products WHERE stock_quantity = 0
);

NOT EXISTS and EXISTS work here too, and are the safer choice for the negated case for the reason the subqueries lesson gives. What is not portable is joining directly in an UPDATE or DELETE — every engine spells that differently, and one of them cannot do it at all. The dialect page on it covers all five.

Transactions

A transaction groups statements so they all succeed or all roll back. Until you commit, you can inspect the result and undo the change.

BEGIN;

UPDATE coupons
SET used_count = used_count + 1
WHERE code = 'WELCOME10';

SELECT code, used_count FROM coupons WHERE code = 'WELCOME10';

ROLLBACK;

BEGIN starts the transaction, the SELECT shows the incremented coupon count, and ROLLBACK restores it. Replacing ROLLBACK with COMMIT makes the change permanent.

SELECT code, used_count FROM coupons WHERE code = 'WELCOME10';

For a risky change on a real database, open a transaction, run the statement, verify the affected row count, then commit. In Planche, Connection › Manual transactions keeps a transaction open and adds Commit and Rollback controls to the toolbar.

What you can do now

Everything except find out why a query is slow — which is the last lesson.