The user needs to have an expired subscription to test the renewal flow. The account needs a zero balance to test the insufficient funds error. UPDATE lets you set up these scenarios in seconds. But it is also the most dangerous command after DELETE. One missing WHERE clause and you have updated every row in the table.
-- Update a single column for specific rows
UPDATE users
SET status = 'inactive'
WHERE email = 'test@example.com';
-- Update multiple columns at once
UPDATE users
SET status = 'active',
is_verified = true,
updated_at = NOW()
WHERE id = 42;
-- Update based on a condition
UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
AND created_at < NOW() - INTERVAL 7 DAY;Write the WHERE clause first: WHERE email = 'test@example.com'
Run a SELECT with that WHERE to see which rows will be affected: SELECT * FROM users WHERE email = 'test@example.com';
Verify the row count is what you expect (1 row, not 1000)
Only then write the full UPDATE statement
Run the UPDATE inside a transaction if your database supports it: BEGIN; UPDATE ...; -- verify -- COMMIT;
Verify the update worked: SELECT * FROM users WHERE email = 'test@example.com';
This is the most important warning in this entire chapter. If you run UPDATE users SET status = 'deleted' without a WHERE clause, every single user in the table becomes deleted. In production, this could mean thousands of customers losing access. Always write WHERE first. Always SELECT first. No exceptions.
-- QA Scenario: Test insufficient funds error
UPDATE accounts
SET balance = 0.00
WHERE account_id = 'ACC_TEST_001';
-- QA Scenario: Test expired subscription
UPDATE subscriptions
SET expires_at = '2020-01-01',
status = 'expired'
WHERE user_id = 42;
-- QA Scenario: Test with a really long name (boundary test)
UPDATE users
SET first_name = 'Abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz'
WHERE id = 42;
-- Watch: does the DB truncate it? Does it throw an error?Q: You accidentally ran UPDATE without WHERE on a production table. What do you do?
A: First, do not panic but act fast. Stop any further operations. If you used BEGIN/START TRANSACTION, run ROLLBACK immediately. If not, check if the DBA has point-in-time recovery (PITR) or recent backups. Inform your lead and DBA immediately — do not try to fix it silently. Document exactly what command you ran and when. This is why we always use transactions and SELECT-before-UPDATE. Prevention is better than cure.
Key Point: UPDATE changes existing data. Always write WHERE first, SELECT to preview affected rows, then UPDATE. Never skip this.