DavidSzczesniak
2/12/2018 - 1:54 PM

Updates with Multiple Tables

In a basic UPDATE statement, each update affected one table and used properties of that table to decide what to modify. This snippet goes into detail on updating rows from more than one table, with the possibility of using separate tables to decide what to modify, all in one statement!

advanced_updates.sql
content_copyfile_download
\!h Highlight albums we've played - changing their names to all capital letters:
-- display albums we've played - for reference:
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 |
+----------------------------+

-- the above query turned into an UPDATE statement:
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);

\!h Same limitations as with DELETE:
-- Can't use ORDER BY.
-- Can't use LIMIT.
-- Can't update a table that's read from in a nested subquery.