Database Reference
In-Depth Information
WHERE
bird_families
.
order_id
IS
NULL
AND
cornell_bird_order
=
bird_orders
.
scientific_name
;
This is fairly complicated, so let's reiterate what's happening here: the
UPDATE
statement
tells MySQL to set the
order_id
in the
bird_families
table to the value of the
order_id
of the corresponding row in the
bird_orders
table — but thanks to the
AND
clause, I do the update only where the
cornell_bird_order
equals the
sci-
entific_name
in the
bird_orders
table.
That's plenty to take in, I know. We'll cover this statement in more detail in
Chapter8
.
Let's see the results now. We'll execute the same SQL statement we did earlier, but limit it
to four rows this time to see a bit more:
SELECT * FROM bird_families
ORDER BY family_id DESC LIMIT 4;
+-----------+-----------------+---------------------+----------+
| family_id | scientific_name | brief_description | order_id |
+-----------+-----------------+---------------------+----------+
| 330 | Viduidae | Indigobirds | 128 |
| 329 | Estrildidae | Waxbills and Allies | 128 |
| 328 | Ploceidae | Weavers and Allies | 128 |
| 327 | Passeridae | Old World Sparrows | 128 |
+-----------+-----------------+---------------------+----------+
That seems to have worked. The
order_id
column for the
Viduidae
bird family now
has a value other than NULL. Let's check the
bird_orders
to see whether that's the
correct value:
SELECT * FROM bird_orders
WHERE order_id = 128;
+----------+-----------------+-------------------+-------------+
| order_id | scientific_name | brief_description | order_image |
+----------+-----------------+-------------------+-------------+
| 128 | Passeriformes | Passerines | NULL |
+----------+-----------------+-------------------+-------------+
That's correct. The
order_id
of 128 is for
Passeriformes
, which is what the Cornell
table said is the order of the
Viduidae
family. Let's see whether any rows in
bird_families
are missing the
order_id
:
SELECT family_id, scientific_name, brief_description
FROM bird_families
WHERE order_id IS NULL;
