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.

View Source file sql/ex7.sql Only

/* 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.