Database Reference
In-Depth Information
| 121 | Tinamiformes | Tinamiformes |
| 100 | Anseriformes | Anseriformes |
| 101 | Galliformes | Galliformes |
| 104 | Podicipediformes | Podicipediformes |
+----------+------------------+------------------+
We're testing a
WHERE
clausehere that we'll use later when updating our
bird_families
table. It's worth looking at what a
WHERE
clause give us before we
put all our trust in it and use it in an
UPDATE
statement.
This
WHERE
clause contains two conditions. First, it changes the
bird_families
table
only where the
order_id
hasn't been set yet. That's kind of a sanity check. If I already
set the
order_id
field, there is no reason to change it.
After the
AND
comes the second condition, which is more important. I want to find the
row in my
bird_orders
table that has the right scientific name, the scientific name as-
signed by Cornell. So I check where
cornell_bird_order
equals the
scientif-
ic_name
in the
bird_orders
table.
This shows how, if you want to changedata with
INSERT…SELECT
,
REPLACE
, or
UPDATE
, you can test your
WHERE
clause first with a
SELECT
statement. If this state-
ment returns the rows you want and the data looks good, you can then use the same
WHERE
clause with one of the other SQL statements to change data.
The
SELECT
statement just shown is similar to the one we executed in the previous sec-
tion of this chapter when we queried the
birds
,
bird_families
, and
bird_orders
tables in the same SQL statement. There is, however, an extra option ad-
ded to this statement: the
DISTINCT
option. This selects only rows in which all of the
columns are distinct. Otherwise, because more than five bird families are members of the
Struthioniformes
order, and I limited the results to five rows (i.e.,
LIMIT 5
), we would
see the first row repeated five times. Adding the
DISTINCT
flag returns five distinct per-
mutations and is thereby more reassuring that the
WHERE
clause is correct.
Because the results look good, I'll use the
UPDATE
statement to update the data in the
bird_families
table. With this statement, you can change or update rows of data. The
basic syntax is to name the table you want to update and use the
SET
clause to set the
UPDATE
bird_families
,
bird_orders
SET
bird_families
.
order_id
=
bird_orders
.
order_id