Database Reference
In-Depth Information
Updating Joined Tables
If you want to use the
UPDATE
statementto change the data in multiple tables, or change
data in a table based on criteria from multiple tables, you can use the
JOIN
clause. The
syntax of the
JOIN
clause for
UPDATE
is the same as it is for
SELECT
. So let's go
straight to some practical examples. We'll start with the example at the end of the previ-
ous subsection.
Let's use
UPDATE
with
LEFT JOIN
to locate rows in the
birds
table that don't have a
value in
conservation_status_id
. We could update all of the rows, but let's do
only rows for one bird family,
Ardeidae
(i.e., Herons, Egrets, and Bitterns). First, execute
this
SELECT
statement to test our joins and
WHERE
clause:
SELECT
common_name
,
conservation_state
FROM
birds
LEFT JOIN
conservation_status
USING
(
conservation_status_id
)
JOIN
bird_families
USING
(
family_id
)
WHERE
bird_families
.
scientific_name
=
'Ardeidae'
;
If you're working from the data from the MySQL Resources site, you should have over
150 rows in the results. You'll notice that many of the rows have nothing in the
com-
mon_name
field. That's because there are many bird species for which there are scientific
names, but no common names. Those rows also have no value for the
conserva-
tion_status_id
. There are also a few rows for bird species that do have common
names.
Let's add another row to the
conservation_status
, one for an unknown state. We'll
set these unknown rows to that state. Enter these two SQL statements:
INSERT INTO conservation_status (conservation_state)
VALUES('Unknown');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 9 |
+------------------+
In the first SQL statement here we entered only a value for
conservation_state
.
The defaults for the other columns are fine. We'll use the
UPDATE
statement to set the
rows for the birds in
Ardeidae
to this new state, so we want to know the
conserva-
