SQL from zero · Chapter 24 of 37
Insert or update
By the end of this chapter you can write a change that says what the end state should be, and works whether the row is already there or not. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Building the schema
- 4 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Rohan files a bug: "I double-tapped like and the app showed an error." The log says duplicate key value violates unique constraint "likes_pkey". The database was right, the like was already there. But the app should not care: "meera likes this post" should simply be true afterwards. Vikram's interview question is the same idea: make Rohan an admin of Chai Lovers, whether or not he is in it.
With what you know, you would first SELECT to see if the row exists, then INSERT or UPDATE. That is two statements, and between them another session (a second tap) can insert the same row, so one of them still fails. And a file that worked once fails the second time you run it.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
like-twice.sql: meera likes all of anya's posts, safe to run twice
meera likes every post by anya that is not deleted. Each new like has the created_at 2026-09-01 12:00+05:30.
meera has already liked some of those posts: those likes stay exactly as they are, their created_at too. Running the file a second time must not fail and must add nothing.
- Task 2 · interview question
Interview: join-circle.sql, rohan is an admin of Chai Lovers, in it or not
Afterwards rohan is in Chai Lovers with the role admin. If he was not in it, he is added (joined_at left to its default); if he was, only his role changes. One statement that works either way, and is safe to run twice.
The Check runs it once with rohan already in Chai Lovers, and once with him not in it. Nothing else changes.
- Task 3
recount.sql: June's daily post counts, updated or added
Bring daily_post_stats up to date for June 2026. For each day of June with at least one post that is not deleted, posts is that day's number of such posts, the day by India time (the lab's clock: the date of created_at).
A day already in the table gets the new count; a day not in it is added. Days of June without posts stay exactly as they are, wrong counts and all. Running it twice changes nothing more.
- Task 4
apply-requests.sql: apply every join and leave request
Apply every row of circle_requests to circle_members. A join adds the member to that circle with the role member (joined_at left to its default); if they are already in it, nothing changes, not even their role. A leave removes their membership of that circle; if they are not in it, nothing happens.
circle_requests names the member by username and the circle by name. Leave circle_requests as it is. One MERGE can do it all.
What you will learn
- ON CONFLICT DO NOTHING: insert the row, or skip it quietly if it would clash with a unique key.
- ON CONFLICT DO UPDATE with EXCLUDED: insert the row, or change the row that is already there.
- MERGE: match a list of rows against a table, and insert, update or delete for each match.
More in Building the schema
Build "Insert or update" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.