This book is new. If you'd like to go through it, then join the Learn Code Forum to get help while you attempt it. I'm also looking for feedback on the difficulty of the projects. If you don't like public forums, then email email@example.com to talk privately.
Exercise 42: Deleting with SQL
This is the simplest exercise, but I want you to think for a second before typing the code in. If you had "SELECT * FROM" for SELECT, and "INSERT INTO" for INSERT, then how would you write the DELETE format? You can probably glance down, but try to guess at what it would be, then look.
I'm simply implementing a very complex update of the robot by deleting him and then putting the record back but with dead=0. In later exercises I'll show you how to use UPDATE to do this, so don't consider this to be the real way you'd do an update.
Most of the lines in this script are already familiar to you, with the exception of line 5. Here you have the DELETE, and it has nearly the same format as other commands. You give DELETE FROM table WHERE tests, and a way to think about it is being like a SELECT that removes rows. Anything that works in a WHERE clause will work here.
Deleting Using Other Tables
Remember I said, "DELETE is like 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 deleted 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:
The lines 1-8 are a DELETE command that starts off normally, but then the WHERE clause uses IN to match id columns in pet to the table that's returned in the subquery. The subquery (also called a subselect) is then a normal 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 person_pet table of any pets that don't exist anymore by using NOT IN rather than IN.
How SQL does this is with the following process:
- Runs the subquery in the parenthesis at the end and builds a table with all the columns just like a normal SELECT.
- Treats this table as a kind of temporary table to match pet.id columns against.
- Goes through the pet table and deletes any row that has an id IN this temporary table.
- Combine all of ex2.sql through ex7.sql into one file and redo the above script so you just run this one new file to recreate the database.
- Add onto the script to delete other pets, and insert them again with new values. Remember that this is not how you normally update records and is only for the exercise.
- Practice writing SELECT commands and then put them in a DELETE WHERE IN to remove those records found. Try deleting any dead pets owned by you.
- Do the inverse and delete people who have dead pets.
- Do you really need to delete dead pets? Why not just remove their relationship in person_pet and mark them dead? Write a query that removes dead pets from person_pet.
You'll want to read the DELETE documentation at https://sqlite.org/lang_delete.html for completeness.