Databases for Beginners
by Sergey Okinchuk
Subqueries: a Query Inside a QueryTopic 21

Chapter Five · Changing Data Safely

Changing Data

For three chapters the Marquee's data has sat behind read-only glass: SELECT looks and changes nothing. This chapter hands you the three verbs that write — INSERT, UPDATE, and DELETE — which also means it hands you your first real chance to do damage. So the verbs come bundled with the habits that make them safe: the row counts professionals read, the SELECT they run first, and the most famous eight-character mistake in SQL, staged here in slow motion so it never has to happen to you at work.

4 topics

The verbs themselves are small. INSERT adds rows, and the database's rules audition every new one at the door. UPDATE edits rows that are already there, in place. DELETE removes rows — except in the many real systems where rows are never truly removed at all, only marked cancelled, for reasons an accountant would recognize instantly. All three reuse the WHERE clause you have been writing since Chapter 3, and that reuse cuts both ways: you already know exactly how to aim these verbs, and a WHERE left off by accident aims them at everything.

Four topics carry the chapter. INSERT gives rows life and shows the constraint checks working in real time. UPDATE stages the missing-WHERE disaster deadpan, then teaches the reflex that prevents it. DELETE meets foreign keys pushing back and learns the industry's soft-delete habit. And the last topic opens the bulk doors: how the 800 customers in Lora's old spreadsheet move into the customers table in one command, and how any query becomes a file she can mail to her accountant.

The three writing verbs — and how far each one reaches
INSERTa new row appears
Blast radius: adds only. Existing rows are untouched; a refused row leaves the table exactly as it was.
UPDATEan existing row is edited
Blast radius: changes what WHERE matches. No WHERE means every row in the table.
DELETEa row is gone
Blast radius: removes what WHERE matches. No WHERE empties the table; foreign keys may object first.

Topics in This Chapter

Topic 21
INSERT
Priya joins the mailing list and a row is born. The full form of INSERT, the checks every new row must pass at the door, several rows at once, and why a refused row is the system working.
SQL
Topic 22
UPDATE (and the Missing-WHERE Disaster)
Screening 88 moves to screen 1 — and then the same statement, eight characters shorter, moves all 212. The most famous mistake in SQL, staged in slow motion, and the SELECT-first reflex that prevents it.
SQL
Topic 23
DELETE (and Why Rows Rarely Truly Die)
A test booking goes quietly; a film does not. Foreign keys pushing back, the cancelled_at column that turns deletion into an UPDATE, and why accountants never use erasers.
SQL
Topic 24
Getting Data In and Out in Bulk
Nobody types 800 INSERT statements. CSV in one paragraph, the bulk doors of PostgreSQL, SQLite, and MySQL, the 12 rows that fail the audition, and any SELECT becoming a file.
SQL