Joining and Subquerying Data - Learning MySQL and MariaDB - page 193

tion_status_id for it. To get that value, we issue a SELECT statement withthe
LAST_INSERT_ID() function. It returns the identifier generated from the previous
SQL statement entered, which added a row for the current client connection (i.e., just us).
Let's use that number to set the conservation_status_id in the birds table for
bird species in Ardeidae . If your identification number is different, use what you received
in the following SQL statement:
UPDATE birds
LEFT JOIN conservation_status USING ( conservation_status_id )
JOIN bird_families USING ( family_id )
SET birds . conservation_status_id = 9
WHERE bird_families . scientific_name = 'Ardeidae'
AND conservation_status . conservation_status_id IS NULL ;
This UPDATE statement should have changed almost 100 rows on your server. The joins
here are the same as we used in the previous SELECT statement, in which we discovered
that we did not have a conservation status set for the Great Egret. Notice in the WHERE
clause here that one of the conditions is that conserva-
tion_status.conservation_status_id has a value of NULL. We could have
removed the LEFT JOIN to the conservation_status table and then updated
simply all of the rows for the Ardeidae birds that have a NULL value in the conserva-
tion_status_id column. But that would not have included any rows that might have
other nonmatching values (e.g., a blank column). By including this LEFT JOIN , we up-
dated all of these possibilities. However, it requires the condition that the conserva-
tion_status.conservation_status_id is NULL, the column from the right
table — it will be assumed NULL if not matched.
Because the method of joining tables is the same for both the SELECT statement and the
UPDATE statement, you can easily test the JOIN clauses and WHERE clause using a
SELECT first. When that's successful, you can then execute an UPDATE statement with
the same JOIN and WHERE clauses. That's the best procedure to follow to ensure proper
updating of data when joiningmultiple tables.
Deleting Within Joined Tables
Having used JOIN with SELECT and UPDATE statements,let's look at some practical
examples using DELETE . In Deleting in Multiple Tables , we saw an example of DELETE
with a JOIN . In that example, we wanted to delete the rows where the member Elena
Bokova has a yahoo.com email address from both the humans and the
prize_winners tables from the birdwatchers database. For that purpose, we con-