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
value of each column. This is like the syntax for the SELECT statement in Inserting Em-
phatically . Use the WHERE clause you tested to tell MySQL which rows to change:
UPDATE bird_families , bird_orders
SET bird_families . order_id = bird_orders . order_id
Search WWH ::




Custom Search