INSERT, UPDATE, DELETE & transactions

Write statements change state. In a real environment, the safe workflow is: understand the target set with SELECT, perform the change inside a transaction where possible, verify the result, then commit deliberately.

The site's SQL runner is isolated in memory. Changes persist only within that runner session and Reset restores the sample database.

INSERT

Always list target columns unless there is a very specific reason not to. This makes the statement resilient to schema changes and self-documenting.

INSERT INTO users (id, email, first_name, last_name, status, role, created_at, updated_at)
VALUES (100, 'new@example.com', 'New', 'User', 'pending', 'qa', '2026-08-25T10:00:00', '2026-08-25T10:00:00');

SELECT id, email, status, role
FROM users
WHERE id = 100;

Run the query to see the result.

UPDATE with a bounded predicate

Before UPDATE, run the same WHERE clause as a SELECT and inspect every row it would touch.

UPDATE users
SET status = 'inactive',
    updated_at = '2026-08-25T12:00:00'
WHERE id = 2;

SELECT id, email, status, updated_at
FROM users
WHERE id = 2;

Run the query to see the result.

An UPDATE without WHERE can modify every row.

DELETE with a bounded predicate

DELETE removes rows; DROP removes schema objects. For production work, verify the target set first and consider whether soft deletion/audit requirements apply.

DELETE FROM users
WHERE id = 3;

SELECT id, email
FROM users
ORDER BY id;

Run the query to see the result.

Transactions and atomicity

A transaction groups changes into one logical unit: either all intended changes commit or they can be rolled back. PostgreSQL's tutorial emphasizes this all-or-nothing property.

BEGIN;
UPDATE orders SET status = 'paid' WHERE id = 5;
UPDATE payments SET status = 'captured' WHERE order_id = 5;
COMMIT;

Run the query to see the result.

The exact locking and isolation behavior depends on the database engine.

ROLLBACK and SAVEPOINT

ROLLBACK abandons uncommitted work. SAVEPOINT establishes a point within a transaction that can be rolled back without discarding earlier transaction work. Savepoints are useful in complex migrations and controlled batch operations.

Read-modify-write race conditions

A transaction does not automatically make every application pattern safe. If two transactions read the same current value and then both write based on it, isolation level and locking determine what can happen. Prefer atomic SQL updates or database constraints when possible.

Safe write checklist

1. Identify rows with SELECT.
2. Confirm expected row count and business scope.
3. Use explicit transaction control where appropriate.
4. Execute the mutation.
5. Re-query invariants and affected rows.
6. Commit only after verification; otherwise roll back.

Practice: in the runner, update one pending order to paid, inspect it, then press Reset and confirm the original state returns.