Databases Reference
In-Depth Information
Updates
Now we'll contrive an example using the
music
database to illustrate multiple-table
updates. We've decided to highlight albums we've played. Our method of highlighting
is to change the album's name to all capital letters. To begin, let's display albums we've
played:
mysql>
SELECT DISTINCT album_name FROM
-> album INNER JOIN track USING (artist_id, album_id)
-> INNER JOIN played USING (artist_id, album_id, track_id);
+----------------------------+
| album_name |
+----------------------------+
| Retro - Miranda Sawyer POP |
| Live Around The World |
+----------------------------+
2 rows in set (0.00 sec)
Now, let's put that query into an
UPDATE
statement:
mysql>
UPDATE album INNER JOIN track USING (artist_id, album_id)
-> INNER JOIN played USING (artist_id, album_id, track_id)
-> SET album_name = UPPER(album_name);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 11 Changed: 2 Warnings: 0
Let's look at the syntax: a multiple-table update looks similar to a
SELECT
query. The
UPDATE
statement is followed by a list of tables that incorporates whatever join clauses
you need or prefer; in this example, we've used
INNER JOIN
to bring together the
artist
,
album
, and
track
tables. This is followed by the keyword
SET
, with assignments to in-
dividual columns; in this example, you can see that only one column is modified (to
put the album name in uppercase), so columns in all other tables besides
album
aren't
modified. An optional
WHERE
may in turn follow (but doesn't in this example, since the
USING
clause does it for us).
To illustrate using a
WHERE
clause, here's the previous query rewritten with the join
expressed using
WHERE
:
mysql>
UPDATE artist, album, track, played
-> SET album_name = UPPER(album_name)
-> WHERE artist.artist_id = album.artist_id AND
-> album.artist_id = track.artist_id AND
-> album.album_id = track.album_id AND
-> track.artist_id = played.artist_id AND
-> track.album_id = played.album_id AND
-> track.track_id = played.track_id;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 11 Changed: 2 Warnings: 0
The method that you choose to use is just personal preference, and that might be driven
by the amount of typing you're prepared to do!
As with multiple-table deletes, there are some limitations on updates:
