A DBA must correct a batch of customer rows where the city was entered as 'Pittsburg' instead of 'Pittsburgh' for addresses in Pennsylvania. The production table has millions of rows. Which approach is safest?
Select an answer to reveal the explanation.
Short Explanation
Measure twice, cut once. Run your WHERE clause as a SELECT first so you see exactly which rows you're about to touch, then do the UPDATE inside a transaction and only commit when the numbers match. The trap is the UPDATE without a WHERE, which rewrites every city in the table in one go.
Full Explanation
Safe data modification follows a preview-then-change pattern. A SELECT using the same predicate planned for the UPDATE, for example city = 'Pittsburg' AND state = 'PA', shows exactly which rows will be affected and how many. Running the UPDATE inside an explicit transaction then lets the DBA compare the reported row count with the preview and either COMMIT or ROLLBACK, so a mistaken predicate can be undone before it becomes permanent. Large updates may be split into batches to limit lock duration and log growth. Omitting the WHERE clause updates every row in the table, setting every customer's city to the same value and destroying correct data; this is the classic data-modification mistake. Deleting and reinserting rows is unnecessary and risky: it can violate foreign keys, fire delete triggers, change identity values or lose columns that the reinsert omits, and it generates more logging than an UPDATE. Using the ORM without previewing does not change the risk; the generated SQL is only as correct as the criteria supplied, and skipping the preview removes the check that catches a wrong predicate. Exam caveat: a recent backup and, ideally, a test run in non-production remain good practice for large production corrections. Operational check: save the preview count, run the UPDATE in a transaction, confirm the affected-row count, commit, and re-run the SELECT to confirm zero remaining misspelled rows.