INSERT, UPDATE, DELETE: Modifying Data

Reads are forgiving. Writes are forever. In 2017 a GitLab engineer typed rm -rf on the wrong server and wiped 300GB of production data. The SQL equivalent is DELETE FROM users without a WHERE. This lesson teaches you the syntax — and the muscle memory that prevents disaster.

Learning Objectives

After this lesson, you will be able to:

Use INSERT INTO to add single and multiple rows to a table
Use UPDATE SET WHERE to modify existing rows safely
Use DELETE FROM WHERE to remove rows without dropping the table
Explain why WHERE clauses are critical for UPDATE and DELETE statements
Wrap multiple changes in a transaction with BEGIN, COMMIT, and ROLLBACK
Use INSERT OR REPLACE / INSERT OR IGNORE / ON CONFLICT (Postgres UPSERT) for conflict handling

Why

Why This Matters

GitLab 2017, GitHub 2018, AWS S3 2017. Three of the most famous outages of the decade started with a single-letter typo or missing WHERE clause. Postmortems are public reading. The fixes were the same: transactions, peer review, and explicit WHERE always
Every API endpoint that changes state is an INSERT/UPDATE/DELETE. Signup forms, checkout buttons, "remove from cart," deletion-of-account requests. Backend engineers write thousands of these. Most bugs hide in the WHERE clause
Transactions are how money moves. Stripe, Square, banks all guarantee debit + credit happen atomically. BEGIN ... COMMIT is the SQL primitive behind every financial transaction in the world
Postgres INSERT ... ON CONFLICT is the production UPSERT pattern — used millions of times a day in idempotent event ingestion, dbt seed upserts, and dedup steps in every . It's what every modern backend uses for "create-or-update"

Command	Removes Rows	Removes Table	WHERE Clause	Can Rollback	Speed
DELETE	Yes	No	Yes	Yes	Slower (logs each row)
TRUNCATE	Yes	No	No	Depends on DB	Very fast
DROP	Yes	Yes	No	No	Instant

INSERT, UPDATE, DELETE: Modifying Data

After this lesson, you will be able to:

Your Reflection

Continue Learning

Discussion

#INSERT -- Adding New Rows

#Basic INSERT

#Inserting Multiple Rows

#INSERT from a SELECT

#Handling Conflicts

#UPDATE -- Modifying Existing Rows

#Basic UPDATE

#UPDATE with Calculations

#UPDATE with Subqueries

#DELETE -- Removing Rows

#Basic DELETE

#DELETE vs. DROP vs. TRUNCATE

#Transactions -- All or Nothing

#Basic Transaction

#Rollback on Error

#The ACID Properties

#Practical Transaction Patterns

#Putting It All Together

UPSERT, isolation levels, and what production really does