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.