The INSERT Statement | 163

Anew row is created—MySQL reports that one row has been affected—and the value is inserted as the artist_id and Barry Adamson as the artist_name
. You can check with a query:
mysql> SELECT * FROM artist WHERE artist_id = 7;
+-----------+---------------+
| artist_id | artist_name |
+-----------+---------------+
| 7 | Barry Adamson |
+-----------+---------------+
1 row inset sec)
You might be tempted to tryout something like this:
mysql> INSERT INTO artist
VALUES((SELECT 1+MAX(artist_id) FROM artist, "Barry Adamson");
However, this won’t work because you can’t modify a table while you’re reading from it. The query would work if you wanted to INSERT INTO
a different table (here, a table other than artist
).
To continue our example, and illustrate the bulk-loading approach, let’s now insert
Barry Adamson’s album The Taming of the Shrewd and its tracks. First, check the structure of the album table:
mysql> SHOW COLUMNS FROM album;
+------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| artist_id | int) | | PRI | 0 | |
| album_id | int) | | PRI | 0 | |
| album_name | char) | YES | | NULL | |
+------------+-----------+------+-----+---------+-------+
3 rows inset sec)
Second, insert the album using the approach we used previously:
mysql> INSERT INTO album VALUES (7, 1, "The Taming of the Shrewd");
Query OK, 1 row affected (0.00 sec)
The first value is the artist_id
, the value of which we know from creating the artist,
and the second value is the album_id
, which must be 1 because this is the first album we’ve added for Barry Adamson.
Third, check the track table structure:
mysql> SHOW COLUMNS FROM track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int) | | PRI | 0 | |
| track_name | char) | YES | | NULL | |
| artist_id | int) | | PRI | 0 | |
| album_id | int) | | PRI | 0 | |
| time | decimal) | YES | | NULL | |

Download 4.24 Mb.

Share with your friends: