Building tables and changing data · Lesson 14 of 20

INSERT, UPDATE and DELETE

Add, change and remove rows, without wiping the table by accident.

10 minutes2 games

These three commands change data:

INSERT INTO adds rows.
UPDATE … SET … WHERE changes existing rows.
DELETE FROM … WHERE removes rows.

SQL

INSERT INTO students (name, city, xp, joined)
VALUES ('Salma', 'Aswan', 0, '2026-06-01'),
       ('Tarek', 'Cairo', 0, '2026-06-02');

UPDATE students
SET xp = xp + 25
WHERE city = 'Cairo';

DELETE FROM students
WHERE name = 'Hana';

SELECT id, name, city, xp FROM students ORDER BY id;

RETURNING and UPSERT

RETURNING gives back the changed rows, so no second SELECT is needed (great for learning the new id). ON CONFLICT says "if the row exists, update it instead of failing", which is called an upsert. Both look the same in SQLite and PostgreSQL.

SQL

-- Laila is already in course 2; bump her progress instead of failing
INSERT INTO enrollments (student_id, course_id, progress)
VALUES (1, 2, 90)
ON CONFLICT (student_id, course_id)
DO UPDATE SET progress = excluded.progress
RETURNING student_id, course_id, progress;

Spot the bug

This was meant to reward only Omar, but every student got 100. What's wrong?

Exercise

Youssef (id = 5) has NULL xp. Give him 50 XP so it becomes 50 (not NULL), then show his name and xp.

Expected result:

name | xp
Youssef | 50

SQL


SELECT name, xp FROM students WHERE id = 5;

Learn more from

Played 0 of 2 games

SQL