SQL from zero · Chapter 23 of 37
Changing data safely
By the end of this chapter you can add, change and remove many rows at once, choosing them with a query, and try a risky change before you keep it. 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
- 5 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
Circles has its tables, three circles and eleven memberships. Meera has a list of changes for launch day: a new circle for Vikram, everyone who writes about chai joins Chai Lovers, some members become admins, members who left the site leave their circles, and small circles are archived.
With what you know, you would look up each member's id with a SELECT, copy the numbers into an INSERT ... VALUES one row at a time, and hope you copied them right. And an UPDATE or DELETE with a missing or wrong WHERE changes rows you never meant to touch, with no undo.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
new-circle.sql: vikram's Chess Corner, with vikram as its owner
vikram creates a circle: name Chess Corner, description "Openings, endgames and blitz on Fridays." (with the full stop), private, member_limit 20, owned by vikram. Leave created_at to its default.
Then add vikram to it in circle_members with the role owner (joined_at left to its default). Find vikram by his username, never by a number.
Afterwards there is one more circle and one more membership, and nothing else has changed.
- Task 2
chai-members.sql: everyone who wrote a chai post joins Chai Lovers
Every member who has not left the site and wrote at least one post tagged chai that is not deleted joins Chai Lovers with the role member (joined_at left to its default).
Members already in Chai Lovers stay exactly as they are: anya stays its owner. A member with several chai posts joins once. Nothing else changes.
- Task 3
promote.sql: Chai Lovers members who follow its owner become admins
In Chai Lovers, every membership whose role is member, of a member who follows the circle's owner (a row in follows), gets the role admin.
The owner stays owner, members who do not follow the owner stay member, and other circles do not change.
- Task 4
clean-up.sql: members who left leave every circle
Remove the memberships, in every circle, of members who are soft-deleted (their users row has a date in deleted_at).
Their users rows stay exactly as they are. Other memberships stay too.
- Task 5 · interview question
Interview: archive.sql, archive the small circles and return them
Archive (soft delete) every circle that is not archived yet and has fewer than 5 rows in circle_members (the owner counts): set its archived_at to 2026-09-10 09:00 India time, written 2026-09-10 09:00+05:30. Do not delete any rows, not even memberships.
Return the circles you archived. Return two columns: name and archived_at. Any order.
What you will learn
- INSERT ... SELECT: insert every row a query returns.
- UPDATE ... FROM and DELETE ... USING: change or remove rows chosen by a join with other tables.
- RETURNING: see the rows a change wrote or removed.
- BEGIN and ROLLBACK: try a change, look at it, and undo it.
More in Building the schema
Build "Changing data safely" 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.