UPDATE — Modifying Existing Data

INSERT adds rows; UPDATE changes them. Almost every state change in an application is, at the database level, an UPDATE — marking an order as shipped, deactivating a user, applying a discount, recording the moment a password was last changed. As a tester you'll use UPDATE for two reasons: to set up test scenarios that would be a hassle to reach through the UI, and to verify that the application's UPDATEs are doing the right thing. This lesson covers both, and the one habit — always include a WHERE — that separates safe UPDATE from career-defining disasters.

The basic UPDATE

UPDATE users
SET role = 'admin'
WHERE email = 'alice@example.com';

Three pieces, in order:

• UPDATE users — the table being modified.
• SET role = 'admin' — the column(s) to change and their new values.
• WHERE email = 'alice@example.com' — which rows to change. This clause is not optional in practice. Ever.

Run a SELECT before and after to see the effect:

SELECT id, name, email, role FROM users WHERE id = 1;
-- Before: role = 'admin' (already, in our seed data)
-- (If we had a row with role = 'tester', it would now be 'admin'.)

Always-include-WHERE — the one habit that prevents disasters

Forget the WHERE and UPDATE users SET role = 'admin' updates every row in the users table. Suddenly every customer is an admin. The database does not warn you. There is no undo without restoring from backup.

The single most useful habit a SQL beginner can develop is to always write the WHERE before the SET. Some testers go further and prepend a SELECT first to count the affected rows:

-- Step 1: confirm which rows you'll touch
SELECT COUNT(*) FROM users WHERE role = 'viewer';
-- Result: 1
 
-- Step 2: do the UPDATE
UPDATE users SET is_active = FALSE WHERE role = 'viewer';

Two seconds of paranoia, zero career-defining mistakes.

Updating multiple columns

Comma-separate the SET assignments:

UPDATE users
SET role = 'admin', is_active = TRUE
WHERE id = 5;

-- Mark order as shipped and record the timestamp (if you had one)
UPDATE orders
SET status = 'shipped'
WHERE id = 4;

Verify:

SELECT id, status FROM orders WHERE id = 4;
-- Result: 4 | shipped

Updating with calculations

The right-hand side of = can reference the column's current value, or any other column:

-- Apply a 10% discount to every Electronics product
UPDATE products
SET price = price * 0.90
WHERE category_id = 1;

After running, the Electronics prices drop by 10%. SELECT confirms:

SELECT name, price FROM products WHERE category_id = 1;

Result (sample, after the update):

| name                     | price  |
|--------------------------|--------|
| Wireless Mouse           |  22.50 |
| Mechanical Keyboard Pro  | 108.00 |
| USB-C Hub                |  40.50 |
| 27-inch Monitor          | 288.00 |
| Webcam HD                |  67.50 |

You can update multiple columns based on the same row, too — for instance, recording an updated_at whenever you change another column:

UPDATE orders
SET status = 'cancelled', total = 0
WHERE id = 5;

Updating with a subquery

You can compute the WHERE condition (or even the SET value) from another query:

-- Deactivate every user who has never placed an order
UPDATE users
SET is_active = FALSE
WHERE id NOT IN (
    SELECT DISTINCT user_id FROM orders
);

Read it line by line: the subquery returns every distinct user id that appears in orders. The outer UPDATE flips is_active = FALSE on every user not in that list. One paragraph of plain English, three lines of SQL.

Subqueries in UPDATE are powerful, and we'll meet them properly in Chapter 6. For now, the shape — "set X where the rows match a query" — is the takeaway.

Verify before updating — the universal habit

It's worth restating because it pays for itself every single time:

-- 1. SELECT first to see what you'll change
SELECT id, name, role FROM users WHERE role = 'viewer';
 
-- 2. Change just the column, with the same WHERE
UPDATE users SET is_active = FALSE WHERE role = 'viewer';
 
-- 3. SELECT again to confirm the change landed
SELECT id, name, role, is_active FROM users WHERE role = 'viewer';

Three queries, a few seconds, total confidence. You ran the UPDATE that you meant, on the rows that you meant, with the result that you meant.

QA use cases for UPDATE

A handful of patterns testers reach for daily:

-- 1. Promote a test user for permission testing
UPDATE users SET role = 'admin' WHERE email = 'qa-test@local';
 
-- 2. Force an order into a particular state to exercise downstream flows
UPDATE orders SET status = 'shipped' WHERE id = 7;
 
-- 3. Reset a known-good baseline before re-running a regression
UPDATE products SET in_stock = TRUE WHERE id = 3;
 
-- 4. Simulate "user inactivity" to test the dormant-account flow
UPDATE users
SET is_active = FALSE
WHERE email LIKE '%@dormant-test.local';

Setting up test conditions through UPDATE is often the difference between a 30-second test and a five-minute UI workaround. Use it.

A note on "audit" columns

Many production schemas have updated_at, last_modified_by, or similar bookkeeping columns. Some are maintained by the database (via triggers) and some by the application. As a tester, after an UPDATE, check the audit columns — if updated_at didn't move when it should have, the application's update path has a bug.

SELECT id, status, created_at, updated_at FROM orders WHERE id = 4;

If updated_at is older than the moment you ran the UPDATE, something in the write path is silently skipping the bookkeeping.

⚠️ Common Mistakes

• No WHERE clause. Every row updated. Production incident. Restore from backup. Already covered above — this is the one mistake worth being neurotic about.
• WHERE on the wrong column. UPDATE users SET role = 'admin' WHERE id = 5 is fine if you meant id 5 — disastrous if you meant id 1. Run a SELECT with the same WHERE first, every time.
• Updating one row when you needed to update many. Dropped a row from your WHERE list by accident? The UPDATE silently does the smaller-than-intended thing. After every UPDATE, check the affected-rows count the database returns, or run a verification SELECT.

🎯 Practice Task

25 minutes. All against the e-commerce database.

1. Update Bob Patel's role from 'tester' to 'admin'. Verify with a SELECT.
2. Mark order id 4 as 'completed'. Confirm the status changed and no other order moved.
3. Apply a 5% price increase to every Books product (category 2). SELECT before and after to confirm.
4. Deactivate every user whose email ends in '@dormant-test.local'. (There may be no such users in your seed data — write the query anyway and observe that it runs cleanly with zero rows affected.)
5. Try the dangerous version once, only on your local sandbox: run UPDATE users SET is_active = FALSE with no WHERE. Then run UPDATE users SET is_active = TRUE to fix it. Reflect: how confident were you that you could undo the mistake? In production you would not be that confident.
6. Stretch: use a subquery in the WHERE clause to deactivate every user with zero orders. Pattern: WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL).

Next lesson: DELETE — the one command that really deserves the WHERE-clause paranoia, plus the soft-delete pattern that dodges most of the danger entirely.