Database Reference
In-Depth Information
With this change, I can now execute the following SQL statement to copy the data from
the Cornell table to my table containing data on bird families:
INSERT IGNORE INTO
bird_families
(
scientific_name
,
brief_description
,
cornell_bird_order
)
SELECT
bird_family
,
examples
,
bird_order
FROM
cornell_birds_families_orders
;
Look closely at this syntax. It may be useful to you one day. It starts with the normal syn-
tax of the
INSERT
statement, but where we would put the
VALUES
clause, we instead put
a complete
SELECT
statement. The syntax of the
SELECT
portion is the same as we've
used so far in other examples in this topic. It's simple, but neat and very powerful.
Conceptually, you can think of the embedded
SELECT
statement creating multiple rows,
each containing values in the order you specify in the
SELECT
. These values work just
like a
VALUES
clause, feeding values into the parent
INSERT
statement and filling the
columns I carefully specify in the right order.
One thing is different at the start of the previous
INSERT
statement.I've added the
IGNORE
option. I used this because the
bird_families
table already had data in it.
Because the
scientific_name
column is set to
UNIQUE
, it does not permit duplicate
values. If a multiple-row
INSERT
statementlike this encounters any errors, it will fail
and return an error message. The
IGNORE
flag instructs the server to ignore any errors it
encounters while processing the SQL statement, and to insert the rows that may be inser-
ted without problems. Instead of failing and showing an error message, warning messages
are stored on the server for you to look at later. When the server is finished, if you want,
you can runthe
SHOW WARNINGS
statement to see which rows of data weren't inserted
into the table. This is a graceful solution if you just want the server to process the rows
that aren't duplicates and to ignore the duplicates.
Now that the data has been inserted, I'll run the following SQL statement from
mysql
to
look at the last row in the table — the first rows contain the data I entered previously:
SELECT * FROM bird_families
ORDER BY family_id DESC LIMIT 1;
+-----------+-----------------+-----------------+----------+-------------------+
| family_id | scientific_name |brief_description| order_id |
cornell_bird_order|
+-----------+-----------------+-----------------+----------+-------------------+
| 330 | Viduidae | Indigobirds | NULL |
Passeriformes |
+-----------+-----------------+-----------------+----------+-------------------+
