Exercise 42: SQL delete

Exercise 42: Deleting with SQL

Translator: Flying Dragon

Protocol: CC BY-NC-SA 4.0

Proudly using Google Translate

This is the simplest exercise, but I want you to think about it for a second before you type in the code. If you write SELECT as “SELECT * FROM” and INSERT as “INSERT INTO”, how do you write the DELETE format? You can look below, but try to guess what it might be and take a look.

/* 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;
Copy the code

I simply did a very complex update for the robot by deleting it and then putting the record back using Dead =0. In a future exercise, I’ll show you how to do this using UPDATE, so don’t assume this is the real way to UPDATE.

You are already familiar with most lines in this script, except for line 5. Here you have DELETE, which has almost the same format as any other command. You provide DELETE FROM table WHERE tests, and a way to think of it as a SELECT that removes rows. Anything that is valid in the WHERE clause is valid here.

Use other tables to delete

Remember I said: “DELETE is like SELECT, but it removes rows from the table.” The restriction is that you can only delete one table at a time. This means that in order to remove all pets, you need to perform some additional queries and then delete based on them.

One way is to use a subquery that selects the ID you want based on the query you’ve already written. There are other ways to implement it, but for now you can implement it based on what you know:

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;
Copy the code

Lines 1 through 8 are DELETE commands that normally start, but the WHERE clause uses IN to match the ID column IN pet with the table returned IN the subquery. A subquery (also known as a subselect) is a normal SELECT that should look similar to the one you did before when trying to find pets that people own.

IN lines 13 to 16, I then use a subquery to remove any nonexistent pets from the person_pet table, using NOT IN instead of IN.

SQL handles this as follows:

  • Run the subquery in parentheses at the end and create a table with all the columns, just like normalSELECTThe same.
  • Treat this table as a temporary table to matchpet.idThe column.
  • browsepetTable and delete the temporary table (IN) for any row.

Challenge to practice

  • Will allex2.sqltoex7.sqlMerge into a file and re-execute the above script so that you only need to run a new file to recreate the database.
  • Add something to the script to remove the other pets, and then insert them again with the new value. Remember, this is not how you normally update your records, just for practice.
  • To practice writingSELECTCommand, and then place them inDELETE WHERE INTo delete the found record. Try deleting any dead pets you own.
  • Do the opposite, delete people with dead pets.
  • Do you really need to delete dead pets? Why not?person_petTo remove their relationship and mark them dead? Write a query fromperson_petRemove dead pets in.

Learn more

For completeness, you need to read the DELETE document.