Find file
Fetching contributors…
Cannot retrieve contributors at this time
51 lines (37 sloc) 1.92 KB
\chapter{Exercise 9: Updating Data}
You now know the CRD parts of CRUD, and I just need to teach you the Update
part to round out the core of SQL. As with all the other SQL commands
the \ident{UPDATE} command follows a format similar to \ident{DELETE} but
it changes the columns in rows instead of deleting them.
\begin{code}{ex9.sql}
<< d['code/ex9.sql|pyg|l'] >>
\end{code}
In the above code I'm changing my name to "Hilarious Guy", since that's
more accurate. And to demonstrate my new moniker I renamed my Unicorn
to "Fancy Pants". He loves it.
This shouldn't be that hard to figure out, but just in case I'm going to
break the first one down:
\begin{enumerate}
\item Start with \ident{UPDATE} and the table you're going to update, in this
case \ident{person}.
\item Next use \ident{SET} to say what columns should be set to what values.
You can change as many columns as you want as long as you separate them
with commas like \verb|first_name = "Zed", last_name = "Shaw"|.
\item Then specify a \ident{WHERE} clause that gives a \ident{SELECT} style
set of tests to do on each row. When the \ident{UPDATE} finds a match it
does the update and \ident{SETs} the columns to how you specified.
\end{enumerate}
\section{What You Should See}
I'm resetting the database with my \file{code.sql} script and then running
this:
\begin{code}{ex9.sql Output}
<< d['code/ex9.sh-session|pyg|l'] >>
\end{code}
I've done a bit of reformatting by adding some newlines but otherwise
your output should look like mine.
\section{Extra Credit}
\begin{enumerate}
\item Use \ident{UPDATE} to change my name back to "Zed" by my \ident{person.id}.
\item Write an \ident{UPDATE} that renames any dead animals to "DECEASED". If you try to say they are "DEAD" it'll fail because SQL will think you mean 'set it to the column named "DEAD"', which isn't what you want.
\item Try using a subquery with this just like with \ident{DELETE}.
\end{enumerate}