Database Reference
In-Depth Information
In the
SELECT
statement here, I addedan
ORDER BY
clause to order the results set by
the value of the
family_id
. The
DESC
after it indicates that the rows should by ordered
in descending order based on the value of
family_id
. The
LIMIT
clausetells MySQL
to limit the results to only one row. Looking at this one row of data, we can see that the
INSERT INTO…SELECT
statement workedwell.
A Digression: Setting the Right ID
Our
INSERT
from the previous section helped me fill my table with data I took from a
free database, but it's still missing data: the bird order for each bird. I defined my own or-
ders of birds in the
bird_orders
table, giving each order an arbitrary
order_id
.
However, the Cornell data had nothing to do with the numbers assigned when I created
my
bird_orders
table. So now I need to set the value of the
order_id
column to the
right
order_id
from the
bird_orders
table — and to figure out that value, I have to
find the order in the
cornell_bird_order
column.
This is a bit complicated, but I am showing my process here to illustrate the power of rela-
tional databases. Basically, I'll join my own
bird_orders
table to the data I got from
Cornell. I loaded the bird orders from Cornell into a
cornell_bird_order
field. I
have the exact same orders in the
scientific_name
field of my
bird_orders
table. But I don't want to use the scientific name itself when I label each individual bird:
instead, I want a number (an
order_id
) to assign to that bird.
I need to set the value of the
order_id
column to the right
order_id
from the
bird_orders
table. To figure out that value, I have to find the order in the
cor-
nell_bird_order
column.
For that, I'll use the
UPDATE
statement.Before I change any data with
UPDATE
, though,
I'll construct a
SELECT
statement for testing. I want to make sure my orders properly
match up with Cornell's. So I'll enter this on my server:
SELECT DISTINCT bird_orders.order_id,
cornell_bird_order AS "Cornell's Order",
bird_orders.scientific_name AS 'My Order'
FROM bird_families, bird_orders
WHERE bird_families.order_id IS NULL
AND cornell_bird_order = bird_orders.scientific_name
LIMIT 5;
+----------+------------------+------------------+
| order_id | Cornell's Order | My Order |
+----------+------------------+------------------+
| 120 | Struthioniformes | Struthioniformes |
