I think we’ve all experienced a system failure that occurs when we are sure nothing changed. But aside from that, it feels true that the smaller the change you make, the better are the chances that it will have no negative repercussions. Having someone review your proposed change improves its chances of success. And small changes are easier to review with confidence than big ones.
When doing file maintenance on a Linux system, I often end up needing to remove some files:
rm <some_pattern>
How sure am I that will do what I intend? Fortunately, listing and removing files in Linux are very close in syntax. I almost invariably precede an rm with a pattern by ls with the same pattern:
ls <some_pattern>
I review the output, convince myself only the intended files are affected, then use the up arrow key to recall the ls, change ls to rm, and press return to execute. I do not retype the pattern!
I can then press up twice and re-execute the ls to verify the files are gone.
Happily, SQL SELECT and DELETE have a similar relationship: the first finds and the second destroys. If I think I want to do:
DELETE FROM my_table WHERE <some_clause>
I generally first do:
SELECT * FROM my_table WHERE <some_clause>
Then, when I’m confident it selects the right rows, I edit the command changing only SELECT * to DELETE and nothing else and then execute it.
It’s less helpful that SQL SELECT and UPDATE have somewhat different forms so if I want to change data, I can’t just write a query and make a simple edit to update the same rows. At least not in an obvious way.
Recently, someone asked me to review their plan to update some data. They offered a fairly complex SELECT that identified the relevant rows and showed the data that needed to be changed. But then they offered a different query to find rows to update. I spent a few minutes looking at the two queries and decided I could not be confident they found and changed the same rows. A common table expression turned out to be the solution.
The query to find the relevant rows ended up something like:
WITH Changes AS ( -- a complex query)SELECT t.OldValue, c.NewValueFROM table AS tJOIN Changes AS c ON c.ID = t.ID;
Of course, we could have listed more values from t if they were helpful but this was enough to
- Give the developer something safe to iterate on while developing the query logic
- Give me a harmless query to review carefully and run as needed to raise my confidence
Once we both felt that the complex logic in the CTE was correct, they had only to change:
SELECT t.OldValue, c.NewValue
to
UPDATE t SET OldValue = cNewValue
That is an almost trivial change that is easy to review. The complex query could have been tens of lines but it didn’t need to be reviewed again because it didn’t change.
They changed it, I reviewed it, they ran it. It did exactly what we wanted.
We often think of database safety in terms of transactions, permissions, or backups. But human-centric safety is just as critical. Good software engineering requires writing code that is easy for a person to read, review, and trust.
If a peer review requires holding two separate queries in your head to verify they target the exact same rows, the process itself invites error. Wrapping the selection logic in a CTE bridges the gap between shell simplicity and SQL structure: it lets you preview the precise impact before swapping out the verb, giving both author and reviewer total confidence.
Small, atomic changes make code easier to write, safer to review, and far less stressful to execute.