WEBVTT 00:07.610 --> 00:10.790 I will call you back again to another video lecture on MySQL. 00:10.790 --> 00:18.470 And in this lecture I'll go ahead and teach you how to update data and also delete a data in MySQL. 00:19.220 --> 00:19.460 All right. 00:19.490 --> 00:24.830 Let me go ahead and say if you followed me from the beginning of this course and you installed MySQL 00:24.830 --> 00:27.800 newly, say let me say you're a beginner in MySQL. 00:27.800 --> 00:34.340 You don't have any knowledge of this before and you really install this workbench with me. 00:34.370 --> 00:39.920 First of all, move over to edit and click on edit and move to preferences. 00:40.340 --> 00:42.080 Now we get to preferences. 00:42.080 --> 00:45.170 Go ahead and click on SQL editor. 00:45.200 --> 00:48.770 Now if you click on this, just the SQL editor. 00:48.800 --> 00:50.360 Then drag this down. 00:51.260 --> 00:58.160 Over here you can see safe updates, rejects, updates and deletes with that with no restrictions. 00:58.160 --> 01:03.250 So if you install this newly, you're going to see that this is ticked means that you cannot be able 01:03.250 --> 01:11.260 to update or delete anything because it's in safe mode, so go ahead and deselect it. 01:11.320 --> 01:14.860 To remove this and then go ahead and click on okay. 01:14.890 --> 01:19.510 And when you click okay you go ahead and restart your workbench. 01:19.510 --> 01:21.250 Close the workbench and restart. 01:21.280 --> 01:27.550 Because if you try to actually update any data in our database, nothing will happen. 01:27.670 --> 01:35.710 So when you deselect that, you go ahead and restart your workbench and then you come back to this lecture. 01:35.740 --> 01:39.100 Now I'll go ahead and teach you how to update a data in your table. 01:39.100 --> 01:40.150 So to update. 01:40.180 --> 01:43.630 Go ahead and use the update keyword. 01:43.630 --> 01:49.510 And we go ahead and specify say students because we're using the students table. 01:49.510 --> 01:51.940 And go ahead and say set. 01:52.060 --> 01:54.640 So after set whatever we want to update. 01:54.670 --> 02:01.480 Now under the table I have Judge Steve and Judge table has no GP and it has no enrollment date. 02:01.510 --> 02:04.330 So we need to give it a GP. 02:04.360 --> 02:07.110 I also need to add the enrollment dates here. 02:07.110 --> 02:12.150 So what I'm going to say is I want to set the GPA first, then I'll show you how to do both at the same 02:12.150 --> 02:12.540 time. 02:12.540 --> 02:16.650 So say the GPA and let GPB go to 4.1. 02:16.650 --> 02:25.170 And there we go ahead and say if we're going to set this for Judge Steve, we're going to tell if we 02:25.170 --> 02:28.410 are doing this based on the first name or last name or status ID. 02:28.440 --> 02:32.610 So there's got to be a class showing the row you are working with. 02:32.610 --> 02:38.070 So if you are working with row eight you're going to set this to be stress ID equal to row eight. 02:38.070 --> 02:43.500 Or we are strength name or we are first name is equal to George or we are last name is equal to Steve. 02:43.500 --> 02:45.030 So we're going to give it a real class. 02:45.030 --> 02:47.850 So go ahead and see where. 02:47.880 --> 02:49.890 Then I'm going to use the stress ID. 02:49.920 --> 02:52.620 So it's going to be okay. 02:52.620 --> 02:55.470 So we are stress ID is equal to eight. 02:55.470 --> 02:59.310 So over here you can see that George Steve is equal to eight. 02:59.340 --> 03:01.530 Then go ahead and put a semicolon at the end. 03:01.560 --> 03:03.450 Then we're to populate this as well. 03:03.450 --> 03:04.950 So it can be able to see that. 03:04.950 --> 03:11.300 So select all From students, so I had to place a semicolon at the end. 03:11.330 --> 03:14.030 Then go ahead and execute this query and check it out. 03:15.500 --> 03:21.290 So we have one school, uh, it says unknown column A student ID. 03:21.290 --> 03:24.200 So this is students ID and not student ID. 03:24.290 --> 03:29.060 If you go back to that table what we have is a student's ID. 03:29.060 --> 03:30.980 So go ahead and execute this query again. 03:30.980 --> 03:32.660 And we have this here. 03:32.690 --> 03:35.960 Now you can see that Steve is 4.1. 03:36.140 --> 03:36.470 All right. 03:36.500 --> 03:38.090 So we have given a GP right in here. 03:38.090 --> 03:38.900 And that is cool. 03:38.930 --> 03:42.320 Now if we want to add enrollment dates so you can do both of them at the same time. 03:42.320 --> 03:43.010 No problem. 03:43.040 --> 03:47.030 Go ahead and put a set GP equal to 4.1 comma. 03:47.030 --> 03:50.060 And then go ahead and specify these. 03:50.360 --> 03:56.240 Uh let me say in row underscore date. 03:56.270 --> 03:56.660 All right. 03:56.690 --> 03:58.880 So you can actually go ahead and do this in one line. 03:58.910 --> 03:59.330 Right. 03:59.330 --> 04:08.720 So I'm just making these two guidelines say a row underscore dates and uh, late dates be equal to. 04:10.520 --> 04:11.000 Then go ahead. 04:11.000 --> 04:20.240 I say this is a 2099 and the amount is 12 and the day is nine. 04:20.390 --> 04:26.000 So if you can specify this, you can go ahead and execute this query and check it out. 04:26.000 --> 04:35.510 And here we have Steve now has a GPA of 4.1 and in row on 2099 12 zero nine. 04:35.720 --> 04:37.250 So you can see that that is cool. 04:37.280 --> 04:39.080 Go ahead and drag this up a little bit. 04:39.080 --> 04:42.800 I want everything to be showing and this is what we have. 04:42.800 --> 04:45.890 So in this way can we move to actually update. 04:45.890 --> 04:49.970 And then you can also be able to do that individually. 04:50.000 --> 04:51.770 Okay I hope that is cool. 04:52.370 --> 04:55.940 So now let me say what we are cross affects all the columns. 04:55.970 --> 05:01.550 Let me go ahead and say I don't just want to update only to Steve to be a GPA of 4.1. 05:01.550 --> 05:05.510 I want every GPA right in here to be equal to 4.1. 05:05.540 --> 05:07.340 How can you be able to do that? 05:07.340 --> 05:11.540 So this is going to affect everything we have here okay. 05:11.620 --> 05:13.900 If we're able to do that, okay. 05:13.930 --> 05:16.030 It's going to affect everything we have here. 05:16.060 --> 05:23.800 So we're going to change everything from a let me say what will this change. 05:23.800 --> 05:24.340 Everything. 05:24.340 --> 05:25.660 So let me go ahead and say. 05:27.760 --> 05:28.780 Remove this. 05:29.260 --> 05:33.760 And uh go ahead and also remove this. 05:33.760 --> 05:38.470 And then going to say update students set equal to 4.1. 05:38.470 --> 05:40.480 And go ahead and execute this query. 05:40.480 --> 05:46.510 And you can see we have actually changed everything to 4.1. 05:46.660 --> 05:47.320 Wow. 05:47.320 --> 05:53.290 So you can also go back and do this individually. 05:53.320 --> 05:55.600 Just put them back the way they are right. 05:55.600 --> 05:58.360 So we have been able to see how it goes. 05:58.390 --> 06:07.060 Now if I want I can actually update students and I'm going to set the date to like, no, we want to 06:07.060 --> 06:08.770 set everything to no. 06:08.800 --> 06:09.190 Right. 06:09.220 --> 06:16.710 So remember when I put this back again and uh, I have students ID equal to eight, so I can actually 06:16.710 --> 06:20.160 go ahead and say this is equal to null. 06:20.190 --> 06:20.790 Right. 06:20.790 --> 06:25.890 So and if I go ahead and do this query we go back. 06:25.920 --> 06:29.070 We can see we've removed the enrolled dates for Steve. 06:29.070 --> 06:30.840 And that is no. 06:31.290 --> 06:34.650 So in this way you can actually update your students. 06:34.650 --> 06:36.150 You can do that individually. 06:36.150 --> 06:39.150 And uh everything is going to work out fine. 06:39.150 --> 06:44.580 So you can see you can select a full a complete column and that you can alter the complete column. 06:44.580 --> 06:46.170 You can do that individually. 06:46.170 --> 06:48.990 You can do that for a whole table. 06:48.990 --> 06:50.160 So I hope that is cool. 06:50.190 --> 06:54.420 Now let me say I want to delete I want to delete some data. 06:54.420 --> 07:04.110 If I go ahead and say delete and I go ahead and say delete from students. 07:04.410 --> 07:10.020 Now I want to advise you to actually do this, because if you do this, it's going to delete every data 07:10.020 --> 07:10.890 right in here. 07:10.890 --> 07:14.550 And the way you do that, you have to go ahead and create a new data again. 07:14.550 --> 07:21.500 And that just want to delete everything, I want to delete where and what reason. 07:21.530 --> 07:22.760 We are close right now. 07:22.760 --> 07:25.370 So I'm going to say that is from students. 07:25.370 --> 07:26.810 Then I'm going to add via class. 07:26.810 --> 07:29.540 Because if you don't add up class it's going to delete everything. 07:29.540 --> 07:37.340 So we are students underscore ID is equal to its previous record. 07:37.370 --> 07:41.420 And then we go ahead and hit on execute. 07:41.420 --> 07:45.230 And now we don't have Judge Steve in our database again. 07:45.230 --> 07:48.020 So we can see that Judge Steve is no longer there. 07:48.260 --> 07:49.040 All right. 07:49.040 --> 07:52.130 So that is a way you can actually update data on your tables. 07:52.130 --> 07:54.260 You can also delete data from your table. 07:54.260 --> 07:55.970 And that is so cool. 07:56.000 --> 07:57.500 Go ahead and practice that. 07:57.530 --> 08:02.720 Try out this and run with your database and see what awards you are doing right in there. 08:02.720 --> 08:03.920 So that is cool. 08:03.920 --> 08:05.300 So I'm going to say thank you so much. 08:05.300 --> 08:08.600 And I'm going to see you in the next video lecture. 08:17.870 --> 08:18.200 Number.