Updating and Deleting Data - Learning MySQL and MariaDB - page 162

USE birdwatchers ;
For the remainder of the examples in this chapter, you should download the rookery
and the birdwatchers databases from the MySQL Resources site . They will provide
you larger tables on which to work.
Updating Specific Rows
Most of the time, whenyou use the UPDATE statement you will need to include the
WHERE clause to stipulate which rows are updated by the values in the SET clause. The
conditions of a WHERE clause in an UPDATE statement are the same as that of a SELECT
statement. In fact, because they're the same, you can use the SELECT statement to test the
conditions of the WHERE clausebefore using it in the UPDATE statement. We'll see ex-
amples of that soon in this chapter. For now, let's look at a simple method of conditionally
updating a single row.
The humans table contains a row for a young woman named Rusty Osborne . She was
married recently and wants to change her last name to her husband's name, Johnson . We
can do this with the UPDATE statement. First, let's retrieve the record for her. We'll select
data based on her first and last name. There may be only one Rusty Osborne in the data-
base, but there may be a few members with the family name of Osborne . So we would
enter this in the mysql client:
SELECT human_id, name_first, name_last
FROM humans
WHERE name_first = 'Rusty'
AND name_last = 'Osborne';
+----------+------------+-----------+
| human_id | name_first | name_last |
+----------+------------+-----------+
| 3 | Rusty | Osborne |
+----------+------------+-----------+
Looking at the results, we can see that there is indeed only Rusty Osborne, and that the
value of her human_id is 3. We'll use that value in the UPDATE statement to be sure
that we update only this one row. Let's enter the following:
UPDATE humans
SET name_last = 'Johnson'
WHERE human_id = 3;
SELECT human_id, name_first, name_last
FROM humans
WHERE human_id = 3;