DELETE: Removing Records from a Table

Why Existing Records Change

Database records often need correction or revision. A customer may change an email address, a product price may increase, or a song's play count may go up. UPDATE is the SQL statement used to modify values in columns of records that already exist in a table.

How WHERE Selects Rows

The WHERE clause filters the table before the update is applied. The database checks each row against the condition. Rows that match are changed; rows that do not match remain untouched. If several rows satisfy the condition, one UPDATE statement changes all of them. If no row satisfies it, the statement changes nothing.

UPDATE Track
SET plays = 16
WHERE title = 'My Way';

This statement targets rows in Track whose title is exactly My Way and changes their plays value to 16. If multiple rows have that title, all matching rows are updated. If no row has that title, no rows change.

Mapping Columns with SET

The SET clause describes the replacement values. Each assignment names a column, uses an equals sign, and gives the new value. Multiple assignments are separated by commas, so one UPDATE can change several columns in the same row or rows.

UPDATE Customer
SET email = 'new@example.com', phone = '555-0100'
WHERE customer_id = 5;

Here, customer_id = 5 identifies the target customer. Both email and phone are changed in the same operation. The assignments in SET are separated by commas, and their order does not matter because SQL processes them together.

Selected Rows and Every Row

A WHERE clause limits the update to rows that satisfy its condition. Omitting WHERE removes that filter. In that case, every row in the table is modified. This is the critical difference between a targeted update and an all-rows update.

UPDATE Order
SET status = 'processed'
WHERE status = 'pending';

This statement changes every pending order to processed. If 100 orders are pending, all 100 are updated by the one statement. The statement is efficient because it handles matching rows together, but the condition must be checked carefully.

Checking Before Changing

SELECT *
FROM Order
WHERE status = 'pending';

UPDATE Order
SET status = 'processed'
WHERE status = 'pending';

Run the SELECT first and inspect its rows. If it returns exactly the records you expect, reuse the same WHERE clause in the UPDATE. If it returns unexpected rows, revise the condition and test again before making any change.

Mistakes to Avoid

• Omitting the WHERE clause when only selected rows should change

  Without WHERE, every row in Customer is modified.

  Fix: Add a condition that identifies the intended row or rows, then verify that condition with SELECT.

• Using a condition that matches more rows than intended

  Every row matching the condition is updated, not just the row you had in mind.

  Fix: Choose and verify a WHERE condition that targets the intended records.

• Skipping the verification step

  You lose the opportunity to inspect the rows before changing them.

  Fix: Run SELECT with the exact same WHERE clause first.

Practice the Decision

Key Takeaways

1. UPDATE modifies values in records that already exist.
2. SET names the columns and replacement values.
3. WHERE determines which rows are changed; matching multiple rows updates all of them.
4. Without WHERE, every row in the table is modified.
5. Verify the exact WHERE clause with SELECT before running UPDATE.