-> artist_name = "New Order" AND

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 inset 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 Lets 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 individual 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

Download 4.24 Mb.

Share with your friends: