The most expensive SQL mistake is not a syntax error — the database catches those immediately. It is a perfectly valid UPDATE or DELETE that touches far more rows than you intended, usually because the WHERE was wrong or missing. The statement succeeds, the damage is done, and on a live database it can take hours to undo.
This lesson is about habits that make that class of mistake survivable, or impossible. None of them are clever; all of them are mechanical routines you run every time. The goal is for safety to stop requiring vigilance and start being just how you write SQL — preview before you change, back up before you go big, and keep every change small enough to reverse.
flowchart LR P["SELECT<br/>preview rows"] --> U["UPDATE<br/>apply change"] --> V["SELECT<br/>verify result"] style P fill:#0e7490,color:#fff style V fill:#0e7490,color:#fff
Preview with the same WHERE
Before you write a modification, write its WHERE as a SELECT first and look at the rows it matches. The change you plan to make becomes a question you can answer safely: "who exactly gets this raise?" If the preview returns three rows and you expected thirty, you have caught a bug at the only moment it is free to fix — before any data moves.
The discipline is to reuse the exact WHERE in both statements. Write SELECT * FROM employees WHERE department_id = 3, confirm the rows, then turn that same WHERE department_id = 3 into UPDATE employees SET ... WHERE department_id = 3. The preview and the change now describe the same set of rows by construction.
SELECT name, salary FROM employees WHERE department_id = 3; UPDATE employees SET salary = salary + 20000 WHERE department_id = 3; SELECT name, salary FROM employees WHERE department_id = 3 ORDER BY id;
Back up before a bulk change
When a modification touches many rows, snapshot the data first so the change is reversible even if it goes wrong. SQLite does this in one statement with CREATE TABLE ... AS SELECT, which copies rows into a brand-new table:
CREATE TABLE projects_backup AS
SELECT * FROM projects WHERE status = 'active';
Now projects_backup holds the active projects as they were before your change. If the modification misfires, you have the original values to restore from; if it succeeds, you drop the backup once you are confident. The cost is a few kilobytes of storage and one line of SQL — trivial insurance against a mistake that could otherwise be irrecoverable.
flowchart LR
B["CREATE backup<br/>AS SELECT"] --> M["mutate the live table"] --> E{"result ok?"}
E -->|"no"| R["restore from backup"]
E -->|"yes"| OK["done, drop backup"]
style R fill:#b45309,color:#fff
style OK fill:#0e7490,color:#fff
CREATE TABLE projects_backup AS SELECT * FROM projects WHERE status = 'active'; SELECT name, status FROM projects_backup ORDER BY id;
Wrap risky changes in a transaction
A transaction is the undo button from the previous lesson, and it shines exactly here. Open BEGIN, run your modification, then inspect the result with a SELECT before you commit. If the affected rows are what you expected, COMMIT. If they are not — the count is off, a row you cared about moved — ROLLBACK and the live table is exactly as it was, as though the change never ran.
This turns a permanent action into a reversible one. You never have to be certain before you run the statement; you only have to be certain before you commit, and by then you have seen the actual outcome.
Clean up your safety net
Once a change is committed and verified, the backup table has done its job and can go. DROP TABLE projects_backup removes the snapshot and its storage, leaving the schema as you found it.
The discipline matters because leftover backups accumulate — each one is a copy of real data sitting around long after it was needed, confusing the next reader and bloating the database. Snapshot before you change, confirm the change, then drop the snapshot. The backup is scaffolding, not a permanent fixture.
flowchart LR
S["modification"] --> W{"WHERE present and specific?"}
W -->|"yes"| F["few rows changed"]
W -->|"missing or too broad"| ALL["many rows changed"]
style F fill:#0e7490,color:#fff
style ALL fill:#b45309,color:#fff
BEGIN; UPDATE projects SET budget = 0 WHERE status = 'on hold'; ROLLBACK; SELECT name, status, budget FROM projects WHERE status = 'on hold';
The Support team (department_id = 4) all get a 5% raise. Write the UPDATE — multiply each salary by 1.05, scoped to department 4.
UPDATE employees -- SET salary to salary * 1.05 for department 4 ;
Read the current value:
salary * 1.05.Scope it with
WHERE department_id = 4so only three rows change.
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 4;
Before changing the projects table, snapshot the completed projects into a backup table called projects_done. Copy all columns of every project whose status is 'completed'.
CREATE TABLE projects_done -- AS SELECT the completed projects ;
Use
CREATE TABLE name AS SELECT ....Filter the source rows with
WHERE status = 'completed'.
CREATE TABLE projects_done AS SELECT * FROM projects WHERE status = 'completed';
This UPDATE was meant to raise only Sales Reps, but it has no WHERE clause — so it raises everyone. Add a WHERE that targets role = 'Sales Rep'.
UPDATE employees SET salary = salary + 20000;
Without WHERE, every row gets the raise.
Add
WHERE role = 'Sales Rep'to scope it to two people.
UPDATE employees SET salary = salary + 20000 WHERE role = 'Sales Rep';
You are about to run a risky UPDATE on a production table. What is the safest first step?
A SELECT with the identical WHERE shows the precise rows about to change, at zero risk. Catching a wrong WHERE here is free; catching it after an UPDATE means undoing real damage.
Transfer skill: safely archive the completed projects. In one transaction, copy the completed projects into a table called archive, then delete them from the projects table. Both steps commit together.
BEGIN; -- create archive from completed projects -- delete completed projects from projects COMMIT;
CREATE TABLE archive AS SELECT * FROM projects WHERE status = 'completed'makes the snapshot.Then
DELETE FROM projects WHERE status = 'completed', all inside BEGIN ... COMMIT.
BEGIN; CREATE TABLE archive AS SELECT * FROM projects WHERE status = 'completed'; DELETE FROM projects WHERE status = 'completed'; COMMIT;
Recap
- Preview first: write the WHERE as a SELECT and confirm the rows before turning it into an UPDATE or DELETE.
- Back up bulk changes with
CREATE TABLE backup AS SELECT ...so the original data survives a mistake. - Wrap risky changes in a transaction — review the result, then COMMIT to keep it or ROLLBACK to undo it.
- Scope every modification with a specific WHERE; a missing or broad WHERE is how tables get clobbered.
- Safety is a routine, not vigilance: preview, back up, transact, scope.
You now have the full toolkit for modifying data — adding, changing, removing, merging, and doing it all safely. The rest of the course will have you combine these statements with the querying skills from earlier modules.