Warning

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 help@learncodethehardway.org 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
/* make sure there's dead pets */
SELECT name, age FROM pet WHERE dead = 1;

/* aww poor robot */
DELETE FROM pet WHERE dead = 1;

/* make sure the robot is gone */
SELECT * FROM pet;

/* let's resurrect the robot */
INSERT INTO pet VALUES (1, "Gigantor", "Robot", 1, 0);

/* the robot LIVES! */
SELECT * FROM pet;

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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
DELETE FROM pet WHERE id IN (
    SELECT pet.id
    FROM pet, person_pet, person
    WHERE
    person.id = person_pet.person_id AND
    pet.id = person_pet.pet_id AND
    person.first_name = "Zed"
);

SELECT * FROM pet;
SELECT * FROM person_pet;

DELETE FROM person_pet
    WHERE pet_id NOT IN (
        SELECT id FROM pet
    );

SELECT * FROM person_pet;

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:

  1. Runs the subquery in the parenthesis at the end and builds a table with all the columns just like a normal SELECT.
  2. Treats this table as a kind of temporary table to match pet.id columns against.
  3. Goes through the pet table and deletes any row that has an id IN this temporary table.

Exercise Challenge

  1. 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.
  2. 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.
  3. 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.
  4. Do the inverse and delete people who have dead pets.
  5. 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.

Further Study

You'll want to read the DELETE documentation at https://sqlite.org/lang_delete.html for completeness.

Pre-order Learn More Python The Hard Way

When you pre-order Learn More Python The Hard Way, you'll receive the Python 3 Edition as it's being created. All files are DRM free and you can download them to your computer for offline viewing. Digital Download Only! You do not get a physical book.

$29.00

Buy the Online Course

Or, you can read Learn More Python the Hard Way for free right here, video lectures not included.

Other Buying Options

Other buying options coming soon.

No content available for this exercise. You can view all available downloads at your customer account page.