The UPDATE Statement | 171

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 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 column 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 threes 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. Lets 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 inset 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)"

Download 4.24 Mb.

Share with your friends: