1 00:00:00,230 --> 00:00:03,350 Deleting all rows in a table is one thing. 2 00:00:03,350 --> 00:00:07,530 But how about the more useful practice of targeting specific rows? 3 00:00:08,950 --> 00:00:11,880 Let's start with the original delete statement that deletes 4 00:00:11,880 --> 00:00:13,090 all rows in a table. 5 00:00:14,340 --> 00:00:18,340 How do you think we could select the rows we wanted to delete? 6 00:00:18,340 --> 00:00:22,890 Just like a select and update statement when you use a WHERE condition, 7 00:00:22,890 --> 00:00:27,090 you can use the same WHERE conditions on DELETE statements too. 8 00:00:27,090 --> 00:00:29,490 Conditions come in all shapes and sizes. 9 00:00:29,490 --> 00:00:33,730 Remember, some have operators such as equality, inequality, greater than, or 10 00:00:33,730 --> 00:00:34,990 less than. 11 00:00:34,990 --> 00:00:39,840 There's the conditions with the IN keyword for searching within a set of values. 12 00:00:39,840 --> 00:00:42,070 Our ranges would use the BETWEEN keyword. 13 00:00:42,070 --> 00:00:46,010 And finally, there's the LIKE keyword for patterns of characters. 14 00:00:46,010 --> 00:00:46,770 Great news! 15 00:00:46,770 --> 00:00:48,320 Our library has been saved. 16 00:00:48,320 --> 00:00:49,720 It's no longer closing down. 17 00:00:49,720 --> 00:00:53,550 We've restored the database from our backup and here it is. 18 00:00:53,550 --> 00:00:56,600 Unfortunately, we were only able to buy back the books, 19 00:00:56,600 --> 00:00:58,810 apart from the Harry Potter books. 20 00:00:58,810 --> 00:01:01,060 But they're still here in the database. 21 00:01:02,060 --> 00:01:05,020 Here's the books table and its contents. 22 00:01:05,020 --> 00:01:09,430 How would we write the query to retrieve all the Harry Potter books? 23 00:01:09,430 --> 00:01:11,607 We could use the LIKE keyword. 24 00:01:19,691 --> 00:01:21,526 Followed by, Harry Potter. 25 00:01:25,120 --> 00:01:26,050 With a wildcard. 26 00:01:27,820 --> 00:01:31,770 When we run the query, all the Harry Potter books are returned. 27 00:01:31,770 --> 00:01:34,488 Let's update this query to a DELETE statement. 28 00:01:43,053 --> 00:01:46,580 This statement will remove all Harry Potter books. 29 00:01:46,580 --> 00:01:47,080 Let's run it. 30 00:01:48,690 --> 00:01:54,980 When we look at all the books now, all of the Harry Potter books are gone. 31 00:01:54,980 --> 00:01:58,021 Finally, let's do another DELETE example. 32 00:02:00,909 --> 00:02:05,367 Michael, our patron with the id of 4, has moved out of the area, 33 00:02:05,367 --> 00:02:08,938 and no longer requires a library account. 34 00:02:08,938 --> 00:02:11,250 Assuming they've returned all of their books, 35 00:02:11,250 --> 00:02:14,400 our policy is to delete the information from our database. 36 00:02:16,590 --> 00:02:24,155 We can do DELETE FROM patrons WHERE id = 4. 37 00:02:27,535 --> 00:02:29,209 Now they've been removed. 38 00:02:31,523 --> 00:02:37,067 We can delete all the loan history 39 00:02:37,067 --> 00:02:43,403 too by writing DELETE FROM loans WHERE 40 00:02:43,403 --> 00:02:48,750 patron_id = 4; Now there's 41 00:02:48,750 --> 00:02:54,900 no trace of Michael in our database. 42 00:02:56,010 --> 00:02:58,340 Depending on your circumstances, 43 00:02:58,340 --> 00:03:02,810 you can delete multiple rows based on any condition you construct. 44 00:03:02,810 --> 00:03:06,580 Remember, the WHERE conditions are exactly the same as how you'd use them 45 00:03:06,580 --> 00:03:08,380 in a SELECT statement. 46 00:03:08,380 --> 00:03:11,620 Instead of retrieving the rows, you're deleting them. 47 00:03:11,620 --> 00:03:13,160 The change is permanent, so 48 00:03:13,160 --> 00:03:16,520 be sure you're deleting exactly what you want to be removed.