Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

Removing Specific Rows

3:17 with Andrew Chalkley

Like with SELECT and UPDATE statements, you can use a WHERE clause to narrow in on specific rows to remove for a table.

SQL Used

To delete specific rows from a table:

DELETE FROM <table> WHERE <condition>;

Examples:

DELETE FROM users WHERE email = "andrew@teamtreehouse.com";
DELETE FROM movies WHERE genre = "Musical";
DELETE FROM products WHERE stock_count = 0;

See all of the SQL used in Modifying Data With SQL in the Modifying Data With SQL Cheatsheet.

  • 0:00

    Deleting all rows in a table is one thing.

  • 0:03

    But how about the more useful practice of targeting specific rows?

  • 0:08

    Let's start with the original delete statement that deletes

  • 0:11

    all rows in a table.

  • 0:14

    How do you think we could select the rows we wanted to delete?

  • 0:18

    Just like a select and update statement when you use a WHERE condition,

  • 0:22

    you can use the same WHERE conditions on DELETE statements too.

  • 0:27

    Conditions come in all shapes and sizes.

  • 0:29

    Remember, some have operators such as equality, inequality, greater than, or

  • 0:33

    less than.

  • 0:34

    There's the conditions with the IN keyword for searching within a set of values.

  • 0:39

    Our ranges would use the BETWEEN keyword.

  • 0:42

    And finally, there's the LIKE keyword for patterns of characters.

  • 0:46

    Great news!

  • 0:46

    Our library has been saved.

  • 0:48

    It's no longer closing down.

  • 0:49

    We've restored the database from our backup and here it is.

  • 0:53

    Unfortunately, we were only able to buy back the books,

  • 0:56

    apart from the Harry Potter books.

  • 0:58

    But they're still here in the database.

  • 1:02

    Here's the books table and its contents.

  • 1:05

    How would we write the query to retrieve all the Harry Potter books?

  • 1:09

    We could use the LIKE keyword.

  • 1:19

    Followed by, Harry Potter.

  • 1:25

    With a wildcard.

  • 1:27

    When we run the query, all the Harry Potter books are returned.

  • 1:31

    Let's update this query to a DELETE statement.

  • 1:43

    This statement will remove all Harry Potter books.

  • 1:46

    Let's run it.

  • 1:48

    When we look at all the books now, all of the Harry Potter books are gone.

  • 1:54

    Finally, let's do another DELETE example.

  • 2:00

    Michael, our patron with the id of 4, has moved out of the area,

  • 2:05

    and no longer requires a library account.

  • 2:08

    Assuming they've returned all of their books,

  • 2:11

    our policy is to delete the information from our database.

  • 2:16

    We can do DELETE FROM patrons WHERE id = 4.

  • 2:27

    Now they've been removed.

  • 2:31

    We can delete all the loan history

  • 2:37

    too by writing DELETE FROM loans WHERE

  • 2:43

    patron_id = 4; Now there's

  • 2:48

    no trace of Michael in our database.

  • 2:56

    Depending on your circumstances,

  • 2:58

    you can delete multiple rows based on any condition you construct.

  • 3:02

    Remember, the WHERE conditions are exactly the same as how you'd use them

  • 3:06

    in a SELECT statement.

  • 3:08

    Instead of retrieving the rows, you're deleting them.

  • 3:11

    The change is permanent, so

  • 3:13

    be sure you're deleting exactly what you want to be removed.

You need to sign up for Treehouse in order to download course files.

Sign up