Updating selected rows

Being able to change data in all rows of a table is impressive, but in reality, it rarely comes up. Having to update a few rows happens much more often. Filtering out rows we are not interested in can be done with the WHERE clause.

WHERE clause

As usual, a logical expression in the WHERE clause can combine several simple expressions. The only requirement is the following: an expression must produce a BOOLEAN value for each row of a table. Only those rows for which the expression produces TRUE will be updated.

For example, imagine that there is a table named groups. This table stores information about the existing student groups in North-Western County college:

Due to the recent policy update from the college administration, the number of students in all groups taking algebra has to be cut to 40. At the same time, Ms. Gibbs realizes that her literature course is more popular than she thought, so she would like to increase the number of students taking her course up to 40. Let's reflect these changes in the table:

UPDATE groups 
SET capacity = 40 
WHERE 
    course LIKE '%Algebra%'
    OR tutor = 'Laura Gibbs'

This is how the table looks now:

Conclusion

Here is the general template of an update statement:

UPDATE table_name 
SET 
    col1 = expr1, 
    col2 = expr2, 
    …, 
    colN = expr 
WHERE 
    logical_expression;

Using WHERE in the UPDATE operation is easy: it follows the same principle here as it does in other SQL operations. Now it's time to check your knowledge.