Inserting Data Using Queries | 279

The LIMIT is thereto limit the number of rows returned by the SELECT we’ve limited in this example simply for readability, but in practice you’d limit it to 999 because that’s the maximum sequence_id you want to use.
The
SELECT
statement in an INSERT INTO
statement can use all of the features of
SELECT
statements. You can use joins, aggregation, functions, and any other features you choose. You can also query data from one database into another, by prefacing the table names with the database name followed by a period (
) character. For example,
if you wanted to insert the artist table from the music database into anew art database,
you could do the following:
mysql> CREATE DATABASE art;
Query OK, 1 row affected (0.01 sec)
mysql> USE art;
Database changed mysql> CREATE TABLE people (
-> people_id SMALLINT(4) NOT NULL,
-> name CHAR) NOT NULL,
-> PRIMARY KEY (people_id));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO art.people (people_id, name)
-> SELECT artist_id, artist_name FROM music.artist;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates 0 Warnings You can see that the new people table is referred to as art.people
(though it doesn’t need to be, since art is the database that’s currently in use, and the artist table is referred to as music.artist
(which it needs to be, since it isn’t the database being used).
Note also that the column names don’t need to be the same for the
SELECT
and the
INSERT
Sometimes, you’ll encounter duplication issues when inserting with a
SELECT
statement.
This occurs if you try to insert the same primary key value twice it won’t happen in the shuffle table, as long as you automatically allocate anew sequence_id using the auto_increment feature. However, when you try to insert duplicate key values, MySQL
will abort. Let’s force a duplicate into the shuffle table to show the behavior:
mysql> USE music;
Database changed mysql> INSERT INTO shuffle (artist_id, album_id, track_id, sequence_id)

Download 4.24 Mb.

Share with your friends: