Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Start a free Courses trial
to watch this video
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.
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