WEBVTT 1 00:00:00.160 --> 00:00:01.620 Hi guys and welcome back. 2 00:00:01.620 --> 00:00:04.883 In this video we're going to learn about the UPDATE query. 3 00:00:05.890 --> 00:00:07.480 UPDATE looks more or less like this. 4 00:00:07.480 --> 00:00:11.090 Here we've got a UPDATE query that does UPDATE users, 5 00:00:11.090 --> 00:00:12.481 so it's going to change the users table 6 00:00:12.481 --> 00:00:15.810 and it's going to set the surname to Wick 7 00:00:15.810 --> 00:00:17.790 where the surname is currently Smith. 8 00:00:17.790 --> 00:00:19.450 So here's how that's deconstructed. 9 00:00:19.450 --> 00:00:21.560 UPDATE users tells the database 10 00:00:21.560 --> 00:00:24.650 that we want to update rows in this user's table, 11 00:00:24.650 --> 00:00:28.017 then we must tell it the change we want to make to each row. 12 00:00:28.017 --> 00:00:30.980 So we're doing SET surname equal 'Wick'. 13 00:00:30.980 --> 00:00:33.229 So we're going to change the surname column 14 00:00:33.229 --> 00:00:36.760 of every row in the table to Wick. 15 00:00:36.760 --> 00:00:38.550 Naturally, normally you don't want to change 16 00:00:38.550 --> 00:00:40.850 every row in a table to the same value, 17 00:00:40.850 --> 00:00:43.110 so that's where the WHERE clause comes in. 18 00:00:43.110 --> 00:00:47.160 Filter them so only certain rows gets updated. 19 00:00:47.160 --> 00:00:49.150 Remember the WHERE, it's very important. 20 00:00:49.150 --> 00:00:50.850 Otherwise all the rows in your table 21 00:00:50.850 --> 00:00:51.920 are going to get updated. 22 00:00:51.920 --> 00:00:53.370 And if you do make a mistake like that, 23 00:00:53.370 --> 00:00:55.320 don't commit to the transaction 24 00:00:55.320 --> 00:00:57.610 and don't save the SQLite database, 25 00:00:57.610 --> 00:01:00.470 just roll back and you'll be fine, 26 00:01:00.470 --> 00:01:02.530 otherwise you're going to have to recreate your data. 27 00:01:02.530 --> 00:01:04.267 You can update multiple columns as well. 28 00:01:04.267 --> 00:01:07.650 You just separate them by commas, and it's no different, 29 00:01:07.650 --> 00:01:09.490 just set a value for each kind of like this. 30 00:01:09.490 --> 00:01:12.970 Here we've got UPDATE users and then the SET part, 31 00:01:12.970 --> 00:01:15.840 first name equal John, comma, surname equal Wick. 32 00:01:15.840 --> 00:01:17.930 So that's gonna change both columns 33 00:01:17.930 --> 00:01:20.399 first name and surname to John Wick, 34 00:01:20.399 --> 00:01:22.520 and the columns that are gonna change 35 00:01:22.520 --> 00:01:24.930 are the ones that have a current surname of Smith. 36 00:01:24.930 --> 00:01:27.350 So notice that in the filter in the workloads 37 00:01:27.350 --> 00:01:29.763 we're not filtering by surname. 38 00:01:31.260 --> 00:01:32.650 So notice that in the WHERE clause, 39 00:01:32.650 --> 00:01:34.530 we're not filtering by first name, 40 00:01:34.530 --> 00:01:36.440 we're only filtering by surname, but that's okay, 41 00:01:36.440 --> 00:01:39.150 we're gonna change the first name and the surname. 42 00:01:39.150 --> 00:01:42.200 You can see here that in the SET part of this query 43 00:01:42.200 --> 00:01:43.660 we're using the equal sign, 44 00:01:43.660 --> 00:01:46.940 and in the WHERE clause we're also using the equal sign. 45 00:01:46.940 --> 00:01:50.290 And so wasn't the equal sign a comparison operator? 46 00:01:50.290 --> 00:01:51.970 Wasn't it used to compare two values 47 00:01:51.970 --> 00:01:54.377 like we do in the WHERE clause? 48 00:01:54.377 --> 00:01:55.908 Well, we did see that, 49 00:01:55.908 --> 00:02:00.908 but actually it's the same in the SET part of the query. 50 00:02:01.170 --> 00:02:03.760 We use the equal sign to assign essentially 51 00:02:03.760 --> 00:02:06.610 to the column their new value. 52 00:02:06.610 --> 00:02:08.890 So that's just an overloaded operator, 53 00:02:08.890 --> 00:02:10.300 an operator that has two meanings 54 00:02:10.300 --> 00:02:12.350 depending on where it's used. 55 00:02:12.350 --> 00:02:14.710 I don't like this either, but that's just how it is. 56 00:02:14.710 --> 00:02:18.600 In Python we've got equal equal for comparison. 57 00:02:18.600 --> 00:02:22.390 In SQL we just use one symbol to mean both things. 58 00:02:22.390 --> 00:02:24.970 Obviously it's clear that you can't use 59 00:02:24.970 --> 00:02:27.700 comparison operators in the SET part. 60 00:02:27.700 --> 00:02:29.341 So don't do this, 61 00:02:29.341 --> 00:02:32.310 don't do SET first name not equal to John 62 00:02:32.310 --> 00:02:34.108 or set salary less than 35,000, 63 00:02:34.108 --> 00:02:35.650 it doesn't make any sense 64 00:02:35.650 --> 00:02:38.230 and you're going to get an error as well. 65 00:02:38.230 --> 00:02:40.140 All right, so we're back here in the code editor. 66 00:02:40.140 --> 00:02:43.100 We're going to add our UPDATE query to our application 67 00:02:43.100 --> 00:02:45.950 and complete the function that was missing. 68 00:02:45.950 --> 00:02:47.210 So the first thing to do is to 69 00:02:47.210 --> 00:02:52.210 create our SET movie watched query, 70 00:02:52.660 --> 00:02:55.370 and this is going to be UPDATE movies 71 00:02:55.370 --> 00:02:58.880 SET watched equal one, where the title 72 00:02:58.880 --> 00:03:00.720 is equal to question mark. 73 00:03:00.720 --> 00:03:03.030 So this is gonna be the argument. 74 00:03:03.030 --> 00:03:03.863 Now that we've got this, 75 00:03:03.863 --> 00:03:05.373 we can go down to watch movie 76 00:03:05.373 --> 00:03:07.607 and simply do with connection, 77 00:03:07.607 --> 00:03:09.346 connection.execute, 78 00:03:09.346 --> 00:03:11.400 SET movie watched, 79 00:03:11.400 --> 00:03:13.750 and passing the title as the argument. 80 00:03:13.750 --> 00:03:16.190 Again, that is gonna pass in that to here 81 00:03:16.190 --> 00:03:17.415 replacing the question mark, 82 00:03:17.415 --> 00:03:19.380 so we're going to run the UPDATE, 83 00:03:19.380 --> 00:03:22.050 setting the watched column to have a value of one 84 00:03:22.050 --> 00:03:24.800 for those rows where the title equals 85 00:03:24.800 --> 00:03:26.134 what we passed in. 86 00:03:26.134 --> 00:03:27.430 Pretty straight forward. 87 00:03:27.430 --> 00:03:29.660 Now that you know how the UPDATE works, 88 00:03:29.660 --> 00:03:32.810 we are able to set movies to watched. 89 00:03:32.810 --> 00:03:33.780 Thank you for watching, 90 00:03:33.780 --> 00:03:35.330 I'll see you in the next video.