1 00:00:00,150 --> 00:00:04,380 So next step we move on to the you in crud, which is update. 2 00:00:04,410 --> 00:00:07,170 How do we update existing rows in a table? 3 00:00:07,200 --> 00:00:09,090 This is something we do pretty frequently. 4 00:00:09,090 --> 00:00:15,360 We certainly insert and select more more often than updating, but it still is really common to need 5 00:00:15,360 --> 00:00:17,580 to change data that's already in a table. 6 00:00:17,580 --> 00:00:23,460 Whether we have some website and restoring user information, a user changes their password or they 7 00:00:23,460 --> 00:00:30,480 change their username, or we alter some existing order, something in a shopping cart item to get added 8 00:00:30,480 --> 00:00:31,250 or removed. 9 00:00:31,260 --> 00:00:32,250 Things are dynamic. 10 00:00:32,250 --> 00:00:33,690 We update information. 11 00:00:33,690 --> 00:00:35,310 So how do we update? 12 00:00:35,310 --> 00:00:38,640 Instead of SQL, we use an update query. 13 00:00:38,640 --> 00:00:42,750 So update and then the table and then set. 14 00:00:42,750 --> 00:00:44,280 So they kind of go together. 15 00:00:44,280 --> 00:00:51,990 Well, they don't kind of they go together in the same way that we saw insert into and then values. 16 00:00:51,990 --> 00:00:53,130 That's a pair. 17 00:00:53,910 --> 00:00:54,810 Update. 18 00:00:55,560 --> 00:00:58,380 And then some table set is a pair. 19 00:00:58,920 --> 00:01:01,760 So in this case, we have to say two things. 20 00:01:01,770 --> 00:01:04,099 What are we trying to update? 21 00:01:04,110 --> 00:01:06,710 We're changing breed to be short hair. 22 00:01:06,720 --> 00:01:13,590 And then on which rows are we performing that update only where Breed is set to Tabby. 23 00:01:13,950 --> 00:01:19,860 So if we didn't include this, where let me just show you on a different table. 24 00:01:19,860 --> 00:01:25,140 I don't want to mess with our cat's table because we have an exercise coming up, but I think we have 25 00:01:25,140 --> 00:01:26,190 employees. 26 00:01:27,060 --> 00:01:29,190 If I did select staff from employees. 27 00:01:29,980 --> 00:01:42,490 Okay, if I did something like update and then employees set and then let set last name to be Rooster 28 00:01:43,540 --> 00:01:45,670 Beard just like that. 29 00:01:45,670 --> 00:01:52,960 If I had enter here, what we just did was update every single row in the table employees to have a 30 00:01:52,960 --> 00:01:54,590 last name of Rooster Beard. 31 00:01:54,610 --> 00:02:00,970 Now, in our case, that was three, but if there was 10,000 employees or 100,000 employees, they all 32 00:02:00,970 --> 00:02:03,120 would have a new last name of Rooster Beard. 33 00:02:03,130 --> 00:02:09,039 So if we don't specify the where it's totally valid, we don't get an error, but we probably don't 34 00:02:09,039 --> 00:02:10,479 want to do that most of the time. 35 00:02:10,479 --> 00:02:12,820 We usually want to be more precise with our updates. 36 00:02:12,820 --> 00:02:18,220 But if you ever do need to change every row on a table, you would use the syntax, update the table 37 00:02:18,220 --> 00:02:21,970 set, whatever you want to set and you don't have a where at the end. 38 00:02:22,480 --> 00:02:25,480 Now we can also update multiple fields at once. 39 00:02:25,480 --> 00:02:31,780 So let's say I want to set I don't know, we're doing very sad mass layoff. 40 00:02:31,780 --> 00:02:37,900 We're updating everybody to have current status of, I don't know, fired or laid off. 41 00:02:37,900 --> 00:02:44,530 And we're going to set everybody's last name to be Who cares? 42 00:02:44,560 --> 00:02:45,430 Very sad. 43 00:02:45,430 --> 00:02:46,450 We've laid everyone off. 44 00:02:46,450 --> 00:02:49,390 We don't care about their name and their status is going to be laid off. 45 00:02:49,720 --> 00:02:54,130 It's just a stupid example to show you that we can update multiple columns at once. 46 00:02:54,130 --> 00:03:03,310 So we say the table employees and then we're going to set current status to be whatever we want, let's 47 00:03:03,310 --> 00:03:06,880 say, fired or laid off. 48 00:03:07,520 --> 00:03:12,410 And then with a comma, I can also say last name equals. 49 00:03:12,710 --> 00:03:13,910 Who cares? 50 00:03:14,650 --> 00:03:14,940 Okay. 51 00:03:14,960 --> 00:03:18,320 It's kind of a cruel update, but let's do it. 52 00:03:18,890 --> 00:03:21,860 And if I select star from employees. 53 00:03:23,320 --> 00:03:28,930 We see that both of those columns were changed in this case for every single row. 54 00:03:29,140 --> 00:03:32,410 So we don't have to update one piece of a row at a time, one column. 55 00:03:32,410 --> 00:03:34,030 We can update as many as we want. 56 00:03:34,420 --> 00:03:40,660 But I think you'll find you tend to not update a whole bunch of different columns all in one go, but 57 00:03:40,660 --> 00:03:44,950 you can it's just maybe less common in the real world. 58 00:03:45,070 --> 00:03:50,620 So as I said, you don't have to have the where, but we often will have a way or clause at the end 59 00:03:50,620 --> 00:03:52,900 to target what we're updating. 60 00:03:52,900 --> 00:04:00,520 So this case is saying find all of the cats that have breed of tabby and set breed to be short hair. 61 00:04:00,850 --> 00:04:02,440 Or here's another example. 62 00:04:02,620 --> 00:04:10,540 We're going to update all the cats in this table that have name of Misty to have age of 14. 63 00:04:10,540 --> 00:04:12,000 So let me show you that one. 64 00:04:12,010 --> 00:04:15,810 Let's go back to our cats Select star from cats. 65 00:04:15,820 --> 00:04:17,110 So here's Misty. 66 00:04:17,140 --> 00:04:22,990 I can select her, although I'm not going to use select I'm going to use where along with update. 67 00:04:22,990 --> 00:04:30,370 So update cats and I want to set her age to be 14 because she had a birthday. 68 00:04:30,400 --> 00:04:31,810 It's 13 right now. 69 00:04:31,810 --> 00:04:33,730 I'm going to set it to be 14. 70 00:04:34,270 --> 00:04:38,920 Again, if I just hit enter now, every single cat would have age of 14, but I'm going to narrow it 71 00:04:38,920 --> 00:04:42,460 down and say where name is equal to Misty. 72 00:04:43,730 --> 00:04:49,400 And if I do a select star from Cats, we see Misty is now 14 years old. 73 00:04:50,690 --> 00:04:53,440 So that's the basic concept of how we update. 74 00:04:53,450 --> 00:05:00,680 We use update some table name like cats, and then we set some values so we can say column equals some 75 00:05:00,680 --> 00:05:01,400 vowel. 76 00:05:01,430 --> 00:05:04,130 Another call equals some other vowel. 77 00:05:04,140 --> 00:05:11,210 And then often we narrow, we clarify where we're trying to update using the where clause. 78 00:05:11,210 --> 00:05:19,370 So only where name is equal to chicken or age is equal to 12, whatever we want. 79 00:05:19,370 --> 00:05:24,410 That's the basic pattern update with set and then usually aware at the end.