Learning MySQL

The UPDATE Statement

The UPDATE statement is used to change data. In this section, we show you how to update one or more rows in a single table. Multitable updates are discussed in Chapter 8.

If you’ve deleted rows from your music database, reload it by following the instructions in Loading the Sample Databases” in Chapter 3. You need a copy of the unmodified music database to follow the examples in this section.

Examples

The simplest use of the UPDATE statement is to change all rows in a table. There isn’t much need to change all rows from a table in the music database—any example is a little contrived—but let’s do it anyway. To change the artist names to uppercase, you can use:

mysql> UPDATE artist SET artist_name = UPPER(artist_name);
Query OK, 6 rows affected (0.04 sec)
Rows matched: 6  Changed: 6  Warnings: 0

The function UPPER() is a MySQL function that returns the uppercase version of the text passed as the parameter; for example, New Order is returned as NEW ORDER. You can see that all six artists are modified, since six rows are reported as affected. The function LOWER() performs the reverse, converting all the text to lowercase.

The second row reported by an UPDATE statement shows the overall effect of the statement. In our example, you see:

Rows matched: 6  Changed: 6  Warnings: 0

The first column reports the number of rows that were retrieved as answers by the statement; in this case, since there’s no WHERE or LIMIT clause, all six rows in the table match the query. The second ...