Exercise 8: 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 the pets you need to do some additional queries and then delete based on those.

One way you do this is with a sub-query that selects the ids you want delete based on a query you've already written. There are other ways to do this, but this is one you can do right now based on what you know:

View Source file ex8.sql Only

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 sub-query. The sub-query (also called a sub-select) 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 sub-query to clear out the person_pet table of any pets that don't exist anymore by using NOT IN rather than IN.

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 database called mydata.db and I'm using a conglomerate SQL file named code.sql 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 sqlite3 -header -column -echo to get nicer output for the tables and to see the SQL that's being run. To create the code.sql file you can use any text editor you want, or do this:

cat ex2.sql ex3.sql ex4.sql ex7.sql > code.sql

Once you have the code.sql file this should work the same:

View Source file ex8.sh-session Only

$ sqlite3 mydata.db < code.sql
# ... cut the output for this ...
$ sqlite3 -header -column -echo mydata.db < ex8.sql 
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;
person_id   pet_id    
----------  ----------
0           0         
0           1         

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

SELECT * FROM person_pet;
$

You should see that after you DELETE the SELECT returns nothing.

Study Drills

• 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.

Portability Notes

Depending on the database, sub-select will be slow.