WEBVTT 0 00:00.840 --> 00:07.470 Now in the last lesson we looked at how you would read data from your database and how you would select 1 00:07.500 --> 00:10.320 pieces of data based on certain criteria. 2 00:10.530 --> 00:17.910 In this lesson we're going to tackle the 'U' in CRUD which is how do you update records in your database? 3 00:18.000 --> 00:24.750 So at the moment if we look at the entire products table, you remember that the price for our pencil 4 00:25.020 --> 00:29.790 is set as null because when we created this record we didn't have a price yet. 5 00:29.850 --> 00:31.390 So we left it blank. 6 00:31.470 --> 00:39.930 If I wanted to update that record to now provide a value for the price of my pencil then I can use the 7 00:39.960 --> 00:41.490 UPDATE statement. 8 00:41.490 --> 00:43.730 And this is how the syntax looks. 9 00:43.860 --> 00:50.760 We use the UPDATE keyword and we specify the table we want to update and then we set the values that 10 00:50.760 --> 00:58.740 we want to update and we can specify a particular condition to only update the records that we were 11 00:58.740 --> 01:00.440 interested in updating. 12 01:00.480 --> 01:08.070 So in our case we would write something like UPDATE and the table we want to update is of course again 13 01:08.070 --> 01:11.480 the products table and then we write set 14 01:11.730 --> 01:19.440 and we want to set the price to equal 0.8 as the price of our pencil. 15 01:19.440 --> 01:25.830 Now this is quite a dangerous statement because if I hit run right now it's going to set everything 16 01:25.860 --> 01:29.290 in my table to have a price of 0.8. 17 01:29.520 --> 01:36.590 So it's really important that you specify the WHERE statement so that it only sets the price of 18 01:36.590 --> 01:42.470 0.8 where the record has an id equal to 2. 19 01:42.930 --> 01:49.740 So now it's going to find this particular record and only set its price to 0.8. 20 01:49.740 --> 01:57.210 So now if I hit run and let's see our products table again, you can see our pencil now has a price and 21 01:57.210 --> 01:59.620 we don't have any null values anymore. 22 02:00.740 --> 02:08.300 Now what if you wanted to change the table instead of a particular record? Say in our product table 23 02:08.300 --> 02:14.450 what if we also wanted to keep track of the stock of each of our products? Well then we would need to 24 02:14.450 --> 02:17.540 add a new column. In SQL 25 02:17.580 --> 02:21.540 that means we need to alter the table. 26 02:21.540 --> 02:23.850 And this is the ALTER table syntax. 27 02:23.850 --> 02:29.480 It's used to add, delete or modify columns in an existing table which is exactly what we want 28 02:29.550 --> 02:30.260 right? 29 02:30.270 --> 02:32.470 And this is what you need to write. 30 02:32.550 --> 02:35.320 ALTER TABLE provide the table name 31 02:35.490 --> 02:38.980 and then you add the column name and data type. 32 02:39.000 --> 02:49.320 So over here we would say ALTER TABLE and the table is the products table and then we say add the column 33 02:49.320 --> 02:55.670 name that's called stock and then the data type will be simply an integer. 34 02:55.670 --> 03:02.660 So now if we hit run and we view our products table you can see we now have an extra column tagged on 35 03:02.660 --> 03:06.170 at the end but they both have null values. 36 03:06.410 --> 03:14.420 So as a challenge, I want you to update both of these records so that they will have a stock value and 37 03:14.420 --> 03:18.090 the value of the stock should correspond to what you see in this table. 38 03:18.110 --> 03:26.350 So we have 32 pens and 12 pencils to sell. Pause the video and try to complete this challenge. 39 03:28.080 --> 03:28.430 All right. 40 03:28.440 --> 03:34.200 So there shouldn't have been too hard because you can always head back to the documentation and check 41 03:34.200 --> 03:37.910 out the syntax for the update statement. In our code 42 03:37.920 --> 03:43.080 we need to specify UPDATE the products table 43 03:43.080 --> 03:53.560 and we're going to set the stock to equal 32 for the pen and we're only going to set the stock to 32 44 03:54.250 --> 03:57.960 where the id is equal to 1. 45 03:57.970 --> 04:06.360 So now if we hit run then you can see our pen now has a stock value and the pencil has a stock of 12. 46 04:06.520 --> 04:15.430 So let's clear the screen and let's update the products table and set the stock to equal 12 where the 47 04:15.430 --> 04:19.130 id is equal to 2. 48 04:19.230 --> 04:26.820 And now we have stock values for both of our products and our table is now complete without any null 49 04:26.820 --> 04:28.320 values. 50 04:28.590 --> 04:32.190 But you can start to see how painful it can be 51 04:32.370 --> 04:37.890 updating some of these values and especially when you haven't accounted for it when you created the 52 04:37.890 --> 04:38.400 schema.