sqlstreak

SQL INSERT, UPDATE and DELETE

Reading data is half of SQL; the other half is changing it. These statements are simple, but a missing WHERE can change every row, so it pays to learn the safe habits early. The examples here run in your browser and never save anything, so experiment freely.

INSERT rows

List the columns, then the values in the same order. Several value lists add several rows. RETURNING hands back the inserted rows, which is handy for generated ids (PostgreSQL, and SQLite 3.35+).

INSERT INTO pets (id, name, species, breed, age, weight_kg, adopted, arrived_on)
VALUES (21, 'Tango', 'dog', NULL, 2, 12.5, false, '2026-11-17'),
       (22, 'Pip', 'rabbit', 'Lionhead', 1, 1.4, false, '2026-11-17')
RETURNING id, name;

UPDATE with WHERE

UPDATE … SET … WHERE … changes matching rows. You can use the old value on the right-hand side (age = age + 1). Leave out WHERE and every row changes.

UPDATE pets
SET adopted = true
WHERE id = 1
RETURNING id, name, adopted;

DELETE with WHERE

DELETE FROM … WHERE … removes matching rows. DELETE FROM pets; with no WHERE empties the table.

DELETE FROM pets
WHERE species = 'bird'
RETURNING id, name;

Upsert with ON CONFLICT

INSERT … ON CONFLICT (key) DO UPDATE inserts a row, or updates it if the key already exists. excluded is the row you tried to insert. Use DO NOTHING to skip duplicates. PostgreSQL and SQLite share this syntax.

INSERT INTO pets (id, name, species, breed, age, weight_kg, adopted, arrived_on)
VALUES (3, 'Pickles', 'dog', NULL, 7, 23.0, false, '2025-09-21')
ON CONFLICT (id) DO UPDATE SET weight_kg = excluded.weight_kg
RETURNING id, name, weight_kg;

Safe habits

Write the WHERE first and run it as a SELECT to see exactly which rows you'll touch. On real databases, wrap risky changes in a transaction (BEGIN; … COMMIT;) so you can ROLLBACK if the row count looks wrong.

Common mistakes

  • UPDATE or DELETE without WHERE.

  • Inserting values in a different order from the column list.

  • Forgetting a NOT NULL column, which rejects the whole insert.

Questions

How do I undo an UPDATE?+

Only with a transaction you haven't committed (ROLLBACK) or a backup. That's why checking with SELECT first matters.

DELETE vs TRUNCATE vs DROP?+

DELETE removes rows (optionally filtered). TRUNCATE (PostgreSQL) quickly removes all rows. DROP TABLE removes the table itself.

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now