-> SELECT artist_id, album_id, track_id, 1 FROM track LIMIT 1

Query OK, 0 rows affected (0.00 sec)
Records: 1 Duplicates 1 Warnings 0
MySQL doesn’t complain, but it does report that it encountered a duplicate. Prior to
MySQL 4.0.1, the
IGNORE
mode was the default behavior, but for later versions, you have to add the keyword if you want duplicates to be ignored.
Finally, note that for versions of MySQL older than 4.0.14, you couldn’t insert into a table that’s listed in the
SELECT
statement, since the
SELECT
would find the newly inserted rows and try to insert them again. On newer systems, you still need to avoid duplicate primary keys:
mysql> INSERT INTO artist SELECT artist_id,artist_name FROM artist;
ERROR 1062 (23000): Duplicate entry '1' for key but you can modify values in the
SELECT
statement to get a different primary key value and insert it back into the same table:
mysql> INSERT INTO artist SELECT 10*artist_id,artist_name FROM artist;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates 0 Warnings Here, we’re copying the rows but multiplying their artist_id s by 10 before we insert them. This is the result:
mysql> SELECT * FROM artist;
+-----------+---------------------------+
| artist_id | artist_name |
+-----------+---------------------------+
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 3 | Miles Davis |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
| 6 | Kylie Minogue |
| 60 | Kylie Minogue |
| 50 | The Stone Roses |
| 40 | The Rolling Stones |
| 30 | Miles Davis |
| 20 | Nick Cave & The Bad Seeds |
| 10 | New Order |
+-----------+---------------------------+
12 rows inset sec)

Download 4.24 Mb.

Share with your friends: