1 00:00:00,430 --> 00:00:03,740 Now it's one thing changing all entries in a table, 2 00:00:03,740 --> 00:00:06,220 how about targeting specific rows? 3 00:00:06,220 --> 00:00:07,650 I can see that being more useful. 4 00:00:09,060 --> 00:00:13,800 Let's start with our original UPDATE statement that updates all the rows. 5 00:00:13,800 --> 00:00:17,420 How do you think we should select the rows where we want to update them? 6 00:00:17,420 --> 00:00:21,550 Well, just like a SELECT statement where you can use a WHERE clause, 7 00:00:21,550 --> 00:00:26,670 you can do the same thing with WHERE conditions on UPDATE statements. 8 00:00:26,670 --> 00:00:28,980 This time, instead of reading the rows, 9 00:00:28,980 --> 00:00:31,700 you're updating the rows that match this criteria. 10 00:00:32,790 --> 00:00:36,230 Remember, conditions come in all shapes and sizes. 11 00:00:36,230 --> 00:00:40,710 Some have operators such as a quality and inequality, greater than or 12 00:00:40,710 --> 00:00:42,417 less than to name a few. 13 00:00:42,417 --> 00:00:46,740 Then there's searching in sets of values with the IN keyword. 14 00:00:46,740 --> 00:00:48,810 There's also conditions where you can search for 15 00:00:48,810 --> 00:00:50,820 ranges with the BETWEEN keyword. 16 00:00:51,880 --> 00:00:55,650 Finally, you could use the LIKE keyword where you could search for 17 00:00:55,650 --> 00:00:57,490 patterns of characters. 18 00:00:57,490 --> 00:01:01,330 If you've taken the prerequisites or have the prerequisite knowledge for 19 00:01:01,330 --> 00:01:04,990 this course, you should be familiar with these conditions. 20 00:01:04,990 --> 00:01:08,610 Let's use some of these conditions to update rows. 21 00:01:08,610 --> 00:01:12,210 Let's look for some missing information in our books table. 22 00:01:14,150 --> 00:01:16,210 As you can see in the last entry, 23 00:01:16,210 --> 00:01:20,350 Animal Farm, there’s a missing value in the genre column. 24 00:01:20,350 --> 00:01:24,520 What condition would we use in order just to select this row? 25 00:01:24,520 --> 00:01:27,886 The most unique value would be the ID of 20. 26 00:01:37,082 --> 00:01:42,081 Other books could have the name, Animal Farm, or the same author, 27 00:01:42,081 --> 00:01:44,020 or released in 1945. 28 00:01:44,020 --> 00:01:46,910 Let's update the genre of Animal Farm to Classic. 29 00:01:58,720 --> 00:02:01,621 All right, let's take another look at Animal Farm again. 30 00:02:13,711 --> 00:02:15,730 It's now a Classic. 31 00:02:15,730 --> 00:02:18,390 Let's update our loans table too. 32 00:02:18,390 --> 00:02:21,320 Let's take a look at it and see what values we're missing. 33 00:02:22,500 --> 00:02:25,650 Let's say the patron with the ID of 1 returned 34 00:02:25,650 --> 00:02:29,520 all their books that they've loaned, books 4 and 8. 35 00:02:29,520 --> 00:02:34,264 Let's imagine today is December 18th, 2015. 36 00:02:35,740 --> 00:02:40,276 So let's start with UPDATE loans 37 00:02:40,276 --> 00:02:44,945 SET returned_on = 38 00:02:44,945 --> 00:02:50,438 "2015-12-18". 39 00:02:50,438 --> 00:02:53,090 Now to the WHERE clause. 40 00:02:54,590 --> 00:03:00,943 Well I could do something like this, WHERE patron_id = 1, 41 00:03:00,943 --> 00:03:04,862 and this will update the rows we need. 42 00:03:04,862 --> 00:03:08,989 But imagine there were hundreds or thousands of rows, 43 00:03:08,989 --> 00:03:12,944 we may not want to update all the loans for patron 1. 44 00:03:12,944 --> 00:03:16,651 They may have loaned hundreds of books over the years, so 45 00:03:16,651 --> 00:03:19,960 to qualify it further would be better. 46 00:03:19,960 --> 00:03:21,621 I could do, AND, 47 00:03:27,847 --> 00:03:33,190 returned_on IS NULL. 48 00:03:34,380 --> 00:03:37,470 This will only update loans that hadn't been returned for 49 00:03:37,470 --> 00:03:40,950 patron 1, and not any historical ones. 50 00:03:40,950 --> 00:03:47,268 Now you can go even further and 51 00:03:47,268 --> 00:03:54,866 add, AND book_id IN (4, 8). 52 00:03:54,866 --> 00:03:58,760 Just in case if they had rented out a different book and 53 00:03:58,760 --> 00:04:00,130 they hadn't returned that one yet. 54 00:04:00,130 --> 00:04:05,073 But since we can see all entries in the loans table and 55 00:04:05,073 --> 00:04:11,601 all the entries for patron 1, we can simply do, WHERE patron id = 1 56 00:04:15,121 --> 00:04:17,990 And as you can see the returned_on has been updated. 57 00:04:19,450 --> 00:04:21,130 Depending on your circumstances, 58 00:04:21,130 --> 00:04:25,520 you can update multiple rows based on any condition you construct. 59 00:04:25,520 --> 00:04:30,190 The WHERE conditions are exactly how you'd use them in a SELECT statement. 60 00:04:30,190 --> 00:04:33,060 Instead of retrieving rows, you're updating them. 61 00:04:33,060 --> 00:04:37,380 So before running the UPDATE statement, you can try your condition out first with 62 00:04:37,380 --> 00:04:42,340 a SELECT query to make sure that the rows returned are the ones you want to update.