WHERE Clause: Filtering Rows in SQL
UPDATE modifies existing records in a table using the SET keyword to specify columns and new values.
Why Row Selection Matters
An UPDATE statement changes records that already exist in a table. The SET clause says which column values should change, while the WHERE clause determines which rows receive that change. This makes WHERE the critical control for protecting rows that should remain untouched.
What do you think happens?
Suppose a Track table contains several songs. Which rows will be changed by an UPDATE whose condition is title = 'My Way'?
Reveal answer
Answer: Only rows whose title is exactly 'My Way'
The WHERE clause filters the rows. Every row matching the condition is updated, while rows that do not match remain untouched.
Tracing the Target Rows
Think of the WHERE clause as a filter applied to the table before the change is made. The condition is checked against rows in the table. Rows that satisfy it become update targets. Rows that do not satisfy it are left alone. If several rows satisfy the condition, the same UPDATE statement changes all of them.
In this example, SET assigns the value 16 to the plays column. WHERE title = 'My Way' identifies the rows that receive that assignment. If more than one row has that title, all matching rows are updated. If no row matches, the statement changes nothing.
Applying SET to Existing Records
An UPDATE statement names the table, uses SET to specify new column values, and can use WHERE to restrict the rows affected. SET can contain one assignment or several assignments. When several columns must change in the same record or records, separate the assignments with commas.
Changing two customer details
A customer with customer_id = 5 has updated both their email address and phone number. Change both values in the Customer table.
Choose the table: The existing record is in the Customer table, so UPDATE names Customer.
List the new values: The SET clause contains one assignment for email and one assignment for phone. The assignments are separated by a comma.
Target the customer: The WHERE clause uses customer_id = 5, so only the record for that customer is targeted.
The email and phone columns are changed for the customer whose customer_id is 5. Other customer records are not targeted by this condition.
The order of assignments in SET does not matter. The important parts are the column names, their new values, and the WHERE condition that identifies the intended rows.
One Statement, Many Matching Rows
A WHERE clause does not necessarily identify one row. It can match many rows. For example, a condition that selects every pending order can update all pending orders in one operation.
UPDATE Order SET status = 'processed' WHERE status = 'pending';
If 100 orders satisfy the condition, all 100 are updated by this one statement. This is efficient, but it also means that a broad condition can affect many records. The number of intended rows matters as much as the new value.
Selected Rows Versus Every Row
| Statement form | Rows affected |
|---|---|
| UPDATE with WHERE | Rows matching the WHERE condition |
| UPDATE without WHERE | Every row in the table |
The first statement restricts the change to rows whose title is exactly My Way. The second statement has no WHERE clause, so it changes the plays value in every row of Track. Omitting WHERE is not a minor formatting difference; it changes the scope of the operation from filtered rows to the entire table.
Checking Before Changing
A reliable safety practice is to run a SELECT statement with the exact same WHERE clause before running the UPDATE. The SELECT lets you inspect which rows the condition identifies. If the returned rows are the ones you expect, you can proceed with the UPDATE. If unexpected rows appear, revise the WHERE clause and test again.
The SELECT and UPDATE use the same filtering condition. First, inspect the rows selected by title = 'My Way'. Only after confirming that the selection is correct should you apply the new plays value with UPDATE.
Common Update Mistakes
Leaving out the WHERE clause when only selected rows should change
Without WHERE, every row in Track is modified.
Fix:
Add a condition that identifies the intended rows, such as WHERE title = 'My Way'.Assuming a WHERE condition can affect only one row
If multiple rows have that title, all matching rows are updated.
Fix:
Use a condition that represents the intended set of rows, and verify the selection with SELECT first.Changing several columns but forgetting one of the SET assignments
Only email is included in SET, so phone is not changed by this statement.
Fix:
Include each intended column assignment in SET, separated by commas.Running UPDATE before checking the target rows
The condition may match more rows than expected, and the UPDATE changes all of them.
Fix:
Run a SELECT with the exact same WHERE condition and inspect the result first.
Practice the Filter
Write an UPDATE statement that changes the status column to processed for every row in the Order table whose current status is pending. Then describe what would happen if you removed the WHERE clause.
Hints
- Begin with UPDATE followed by the table name.
- Use SET to assign processed to status.
- Use WHERE to select rows whose current status is pending.
- Without WHERE, the assignment would apply to every row in the table.
Before executing your UPDATE, write a SELECT statement that uses the same WHERE condition. What should you inspect in the SELECT result before proceeding?
Hints
- Keep the WHERE condition identical in both statements.
- Check that the returned rows are exactly the records you intend to modify.
Key Takeaways
- UPDATE modifies values in records that already exist in a table.
- SET specifies the columns and new values, and it can contain multiple assignments.
- WHERE filters the rows that receive the update; every matching row is affected.
- Without WHERE, UPDATE modifies every row in the table.
- Run SELECT with the same WHERE clause first so you can verify the intended target rows.
Key Takeaways
- UPDATE changes existing records rather than adding new rows.
- SET identifies the columns and values to change.
- WHERE determines which rows are modified, and one condition can match multiple rows.
- Leaving out WHERE causes every row in the table to be updated.
- Verify the target rows with SELECT before executing UPDATE.