Permalink
Switch branches/tags
Nothing to show
Find file
Fetching contributors…
Cannot retrieve contributors at this time
73 lines (57 sloc) 2.93 KB
\chapter{Exercise 8: Deleting Using Other Tables}
Remember I said, "\ident{DELETE} is like \ident{SELECT} but it removes
rows from the table." The limitation is you can only delete from one
table at a time. That means to delete all of the pets you need to do
some additional queries and then delete based on those.
One way you do this is with a subquery that selects the ids you want
delete based on a query you've already written. There's other ways
to do this, but this is one you can do right now based on what you
know:
\begin{code}{ex8.sql}
<< d['code/ex8.sql|pyg|l'] >>
\end{code}
The lines 1-8 are a \ident{DELETE} command that starts off normally, but
then the \ident{WHERE} clause uses \ident{IN} to match \ident{id} columns
in \ident{pet} to the table that's returned in the subquery. The subquery
(also called a subselect) is then a normal \ident{SELECT} and it should
look really similar to the ones you've done before when trying to find
pets owned by people.
On lines 13-16 I then use a subquery to clear out the \ident{person\_pet}
table of any pets that don't exist anymore by using \ident{NOT IN} rather
than \ident{IN}.
How SQL does this is with the following process:
\begin{enumerate}
\item Runs the subquery in the parenthesis at the end and build a table
with all the columns just like a normal \ident{SELECT}.
\item Treats this table as a kind of temporary table to match \ident{pet.id}
columns against.
\item Goes through the \ident{pet} table and deletes any row that has an id
\ident{IN} this temporary table.
\end{enumerate}
\section{What You Should See}
I've changed the formatting on this and removed extra output that isn't
relevant to this exercise. Notice how I'm using a new databse called
\file{mydata.db} and I'm using a conglomerate SQL file that has all the
SQL from exercises 2 through 7 in it. This makes it easier to rebuild
and run this exercise. I'm also using \program{sqlite3 -header -column -echo}
to get nicer output for the tables and to see the SQL that's being run.
\begin{code}{ex8.sql Output}
<< d['code/ex8.sh-session|pyg|l'] >>
\end{code}
You should see that after you \ident{DELETE} the \ident{SELECT} returns
nothing.
\section{Extra Credit}
\begin{enumerate}
\item Practice writing \ident{SELECT} commands and then put them in a
\ident{DELETE WHERE IN} to remove those records found. Try
deleting any dead pets owned by you.
\item Do the inverse and delete people who have dead pets.
\item Do you really need to delete dead pets? Why not just remove their
relationship in \ident{person\_pet} and mark them dead? Write a
query that removes dead pets from \ident{person\_pet}.
\end{enumerate}
\section{Portability Notes}
Depending on the database subqueries will be slow. Sometimes they can be
faster depending on how the tables are setup and the type of data you're
querying. There are other ways to do this same thing, but for now just
use this since it's something you can understand.