Safe UPDATE & DELETE Habits

Every database professional has a story about the UPDATE that forgot its WHERE. The difference between a beginner and a pro isn't that the pro never makes mistakes — it's that the pro has habits that catch them before they hurt.

Definition

Safe data modification is a set of working habits for running UPDATE and DELETE so that only the intended rows are affected, and so that any mistake can be detected and undone before it becomes permanent.

The golden rule from the course: ALWAYS use WHERE with UPDATE/DELETE, or EVERY row is changed.

Habit 1 — SELECT first, then change

Write the WHERE as a SELECT and look at the rows before changing them:

-- Step 1: which rows will be affected?
SELECT *
FROM players
WHERE team = 'MI';

2 rows — exactly what we expected. Now reuse the same WHERE:

-- Step 2: change exactly those rows
UPDATE players
SET runs = runs + 50
WHERE team = 'MI';

Habit 2 — use a transaction as a safety net

A transaction is a group of statements that the database treats as one unit of work: either all of it is saved (COMMIT) or none of it is (ROLLBACK).

BEGIN TRANSACTION;

UPDATE players
SET runs = 0;            -- oops, forgot WHERE!

SELECT * FROM players;   -- all zeros... (6 rows affected) was a warning sign

ROLLBACK;                -- undo everything since BEGIN TRANSACTION

SELECT * FROM players;   -- original runs are back

Tip: Run the change, check the "rows affected" count and the data, and only then COMMIT. Don't leave a transaction open for long — it locks the rows for other users.

Habit 3 — target rows by primary key

WHERE id = 4 changes exactly one row, because ids are unique. WHERE name = 'Dhoni' might match zero rows (the name is 'MS Dhoni') or several (two players with the same name).

Habit 4 — check "rows affected"

In T-SQL scripts you can read the count with @@ROWCOUNT right after the statement.

Habit 5 — back up before big changes

Copy the table first (SELECT * INTO players_backup FROM players; in SQL Server), so you can restore any values you damage. In production, make sure a real database backup exists.

Habit 6 — beware "run selection"

In SSMS, if you highlight only part of a script and press F5, only the highlighted text runs. Highlighting just the first line of:

DELETE FROM players
WHERE id = 6;

runs DELETE FROM players — without the WHERE! Some developers keep a single-line habit for dangerous statements, or always type the WHERE before the SET/DELETE part.

Bonus — see what changed with OUTPUT (SQL Server)

DELETE FROM players
OUTPUT deleted.id, deleted.name
WHERE runs < 100;

The OUTPUT clause returns the rows that were deleted (or inserted.* / deleted.* for UPDATE), so you see exactly what happened.

Safe-change checklist

1. ✅ Am I connected to the right database (test vs production)?
2. ✅ Did I run the WHERE as a SELECT first?
3. ✅ Am I inside BEGIN TRANSACTION?
4. ✅ Does "rows affected" match what I expected?
5. ✅ Only then: COMMIT.

SQL Server vs MySQL / PostgreSQL

Common mistakes

• ❌ Testing changes directly on production data. ✅ Use a test copy first.
• ❌ Forgetting to COMMIT — the change is lost when the session ends and rows stay locked meanwhile. ✅ Finish every transaction with COMMIT or ROLLBACK.
• ❌ Writing a different WHERE in the UPDATE than the one you tested with SELECT. ✅ Copy–paste the exact WHERE.

Interview tip

Q: You accidentally ran an UPDATE without WHERE in production. What do you do? A: If it was inside an open transaction, ROLLBACK immediately. If it was already committed, restore the affected values from a backup (or point-in-time restore of the database). Prevent it next time by running changes inside a transaction, testing the WHERE with SELECT, and checking the rows-affected count before COMMIT.