🟢 UPDATE & DELETE

UPDATE & DELETE: Think of UPDATE and DELETE as standing over a class roster with a pencil and an eraser.

Think of UPDATE and DELETE as standing over a class roster with a pencil and an eraser. The twist: you don't name who gets crossed out, you describe them — "everyone with more than 3 absences". Forget to say the description and just say "erase", and the eraser sweeps the entire list. Both share that same trap: omit WHERE and the command applies to every row in the table. In Java `map.put(key, newValue)` modifies only that key's value; in SQL `UPDATE test_results SET status = 'PASS'` changes the status of every single row, because the target in SQL is a WHERE condition, not a key. Isn't a key required in both cases? In Java yes — in SQL no, and that asymmetry is the most common cause of production data disasters. For a QA engineer this risk is especially acute during test cleanup: if you write `DELETE FROM test_results` instead of `DELETE FROM test_results WHERE environment = 'ci-temp'` after a test run, you wipe the entire test history — the CI dashboard goes blank, reports become meaningless, and the team can't explain why last week's results disappeared. Rule: always run a SELECT with the same WHERE clause first and visually confirm the row count before writing UPDATE or DELETE.

Micro Lab: SQL — Update and Delete

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

Step by Step: SQL — Update and Delete

Wrap operations with BEGIN TRANSACTION — make it reversible on error

First view affected records with SELECT * FROM table WHERE condition

Use PRIMARY KEY or unique column when writing WHERE condition

Run UPDATE table SET col=value WHERE condition or DELETE FROM table WHERE condition

Verify the result and COMMIT; if there is a problem, use ROLLBACK

What is the safe SQL update or delete order?

ALWAYS include WHERE with UPDATE and DELETE! Without WHERE, every row in the table is affected. Run a SELECT with the same WHERE first to verify which rows will be changed.

Safe UPDATE and DELETE Practices

Updating (UPDATE) and deleting (DELETE) data can cause irreversible damage. Follow these safety rules: 1. **The Critical WHERE Clause**: Running `UPDATE` or `DELETE` without a `WHERE` filter modifies or deletes **every single row** in the table! (e.g., `DELETE FROM logs;` empties the table completely but keeps its schema/structure). 2. **Verify with SELECT First**: Before running an update or delete, run the exact same `WHERE` condition in a `SELECT` query first. This confirms you are targeting only the intended rows.

🎬 UPDATE Without WHERE: The Classic QA Disaster