Basic SQL - Learning MySQL - page 172

since none of the strings are entirely in uppercase, all six rows are changed. If you repeat
the statement, you'll see a different result:
mysql> UPDATE artist SET artist_name = UPPER(artist_name);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 6 Changed: 0 Warnings: 0
This time, since all of the artists are already in uppercase, six rows still match the
statement but none are changed. Note also the number of rows changed is always equal
to the number of rows affected, as reported on the first line of the output.
Our previous example updates each value relative to its current value. You can also set
columns to a single value. For example, if you want to set all played dates and times to
the current date and time, you can use:
mysql> UPDATE played SET played = NULL;
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11 Changed: 11 Warnings: 0
You'll recall from “Alternative Syntaxes” that since the default value of the played col-
umn is CURRENT_TIMESTAMP , passing a NULL value causes the current date and time to be
stored instead. Since all rows match and all rows are changed (affected), you can see
three 11s in the output.
Using WHERE, ORDER BY, and LIMIT
Often, you don't want to change all rows in a table. Instead, you want to update one
or more rows that match a condition. As with SELECT and DELETE , the WHERE clause is
used for the task. In addition, in the same way as with DELETE , you can use ORDER BY
and LIMIT together to control how many rows are updated from an ordered list.
Let's try an example that modifies one row in a table. If you browse the album database,
you'll notice an inconsistency for the two albums beginning with “Substance”:
mysql> SELECT * FROM album WHERE album_name LIKE
-> "Substance%";
+-----------+----------+-------------------------+
| artist_id | album_id | album_name |
+-----------+----------+-------------------------+
| 1 | 2 | Substance (Disc 2) |
| 1 | 6 | Substance 1987 (Disc 1) |
+-----------+----------+-------------------------+
2 rows in set (0.00 sec)
They're actually part of the same two CD set, and the first-listed album is missing the
year 1987, which is part of the title. To change it, you use an UPDATE command with a
WHERE clause:
mysql> UPDATE album SET album_name = "Substance 1987 (Disc 2)"
-> WHERE artist_id = 1 AND album_id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0