Sample Video Frame

Created by Zed A. Shaw Updated 2024-02-17 04:54:36

Exercise 7: Deleting Data

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.

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

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

/* the robot LIVES! */

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, except for 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.

What You Should See

I'm going to reconstruct the entire database from scratch by replaying all the exercises to this point that you need. This shows you how your work so far should continue to work as you go through the exercises.

$ rm ex7.db
$ sqlite3 ex7.db < ex2.sql
$ sqlite3 ex7.db < ex3.sql
$ sqlite3 ex7.db < ex4.sql
$ sqlite3 -echo ex7.db < ex7.sql
SELECT name, age FROM pet WHERE dead = 1;
DELETE FROM pet WHERE dead = 1;
INSERT INTO pet VALUES (1, "Gigantor", "Robot", 1, 0);

Notice at the end I'm adding the sqlite3 -echo so you can see what statements run and what they produce.

Study Drills

Previous Lesson Next Lesson

Register for Learn SQL the Hard Way

Register today for the course and get the all currently available videos and lessons, plus all future modules for no extra charge.