Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Modifying Data with SQL

Preview

Start a free Courses 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 from 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.

Related Discussions

Have questions about this video? Start a discussion with the community and Treehouse staff.

Sign up

    Related Discussions

    Have questions about this video? Start a discussion with the community and Treehouse staff.

    Sign up

    Deleting all rows in a table is one thing. 0:00
    But how about the more useful practice of targeting specific rows? 0:03
    Let's start with the original delete statement that deletes 0:08
    all rows in a table. 0:11
    How do you think we could select the rows we wanted to delete? 0:14
    Just like a select and update statement when you use a WHERE condition, 0:18
    you can use the same WHERE conditions on DELETE statements too. 0:22
    Conditions come in all shapes and sizes. 0:27
    Remember, some have operators such as equality, inequality, greater than, or 0:29
    less than. 0:33
    There's the conditions with the IN keyword for searching within a set of values. 0:34
    Our ranges would use the BETWEEN keyword. 0:39
    And finally, there's the LIKE keyword for patterns of characters. 0:42
    Great news! 0:46
    Our library has been saved. 0:46
    It's no longer closing down. 0:48
    We've restored the database from our backup and here it is. 0:49
    Unfortunately, we were only able to buy back the books, 0:53
    apart from the Harry Potter books. 0:56
    But they're still here in the database. 0:58
    Here's the books table and its contents. 1:02
    How would we write the query to retrieve all the Harry Potter books? 1:05
    We could use the LIKE keyword. 1:09
    Followed by, Harry Potter. 1:19
    With a wildcard. 1:25
    When we run the query, all the Harry Potter books are returned. 1:27
    Let's update this query to a DELETE statement. 1:31
    This statement will remove all Harry Potter books. 1:43
    Let's run it. 1:46
    When we look at all the books now, all of the Harry Potter books are gone. 1:48
    Finally, let's do another DELETE example. 1:54
    Michael, our patron with the id of 4, has moved out of the area, 2:00
    and no longer requires a library account. 2:05
    Assuming they've returned all of their books, 2:08
    our policy is to delete the information from our database. 2:11
    We can do DELETE FROM patrons WHERE id = 4. 2:16
    Now they've been removed. 2:27
    We can delete all the loan history 2:31
    too by writing DELETE FROM loans WHERE 2:37
    patron_id = 4; Now there's 2:43
    no trace of Michael in our database. 2:48
    Depending on your circumstances, 2:56
    you can delete multiple rows based on any condition you construct. 2:58
    Remember, the WHERE conditions are exactly the same as how you'd use them 3:02
    in a SELECT statement. 3:06
    Instead of retrieving the rows, you're deleting them. 3:08
    The change is permanent, so 3:11
    be sure you're deleting exactly what you want to be removed. 3:13

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

    Sign up

      You need to sign up for Treehouse in order to set up Workspace

      Sign up