WEBVTT 00:07.400 --> 00:07.820 All right. 00:07.820 --> 00:11.690 So let's go ahead and look at uh what conflicts do. 00:11.690 --> 00:12.530 Update. 00:12.530 --> 00:18.470 So in the previous lecture we are going to work with uh conflicts do nothing. 00:18.560 --> 00:21.770 And we use the email and the primary key. 00:21.770 --> 00:29.090 So if we go ahead and uh, say check the email and go ahead and execute this, we have this workout 00:29.090 --> 00:29.510 file. 00:29.540 --> 00:33.020 But now we actually want to do something important. 00:33.050 --> 00:40.370 Imagine you have this, uh, database and someone has entered their details into your server, and now 00:40.370 --> 00:42.080 they want to update that. 00:42.320 --> 00:47.930 I'm sure you might have learned about the update, but are not doing that with a real time. 00:47.930 --> 00:50.510 So how can you be able to do this? 00:50.750 --> 00:56.990 In order to do this, using the old conflict due update, we're going to remove the do nothing, and 00:56.990 --> 01:02.030 now we're going to replace that with a do update. 01:02.720 --> 01:13.380 Now if I go ahead and, uh, this time around or I need to do is let me say this person enters first 01:13.380 --> 01:17.850 name, last name, age, gender, uh, email a latent cool. 01:17.850 --> 01:22.740 And the person comes back and say, right now my email has changed from Steve. 01:22.740 --> 01:26.640 And it is now, uh, just, uh uh. 01:28.830 --> 01:30.270 Memoir that's calm. 01:30.270 --> 01:35.130 And, uh, let's go ahead and, uh, see what actually happens. 01:35.130 --> 01:38.970 And, uh, if you go ahead and run this, you might get into problem. 01:38.970 --> 01:39.840 Let's check it out. 01:40.230 --> 01:41.730 Now there is a problem. 01:41.730 --> 01:53.700 What happens is that when you insert this, you go ahead and, uh, say, what I need to do here is 01:53.700 --> 01:59.940 to set the email to the new email address that the person wants to insert. 02:00.030 --> 02:11.430 And, uh, what are you going to do is you go ahead and uh, say update set and then email a code to 02:12.960 --> 02:14.580 the excluded. 02:16.590 --> 02:17.580 Dot. 02:18.750 --> 02:22.350 So this is email equal event equal sign here. 02:22.350 --> 02:26.010 And the the discredit dot. 02:29.010 --> 02:34.530 Email or I know we have a excluded dot email. 02:34.530 --> 02:39.420 And we're going to set this to the new email that is right in here. 02:39.510 --> 02:42.090 And let's go ahead and run this and check it out. 02:42.120 --> 02:46.110 Now we can drag this and move down. 02:46.110 --> 02:51.150 And now you can see that we have this right in here. 02:51.150 --> 02:54.390 And uh, it is a new one. 02:54.390 --> 03:00.510 And that is just at uh manoa.com. 03:01.200 --> 03:07.860 But what happens is that this actually created a new person. 03:07.890 --> 03:20.380 And what if I want this exact person that is the ID 1104 or 1001 for that is the last portion right 03:20.380 --> 03:24.190 in here to be the extra person without creating a new one. 03:24.220 --> 03:25.660 What am I going to do? 03:25.690 --> 03:36.490 What I need to do is I'll go right in here and I'm going to say students underscore ID and now I'm going 03:36.520 --> 03:42.820 to work on this sent ID and I'm going to choose that ID colon 1014. 03:42.850 --> 03:44.890 And remember this ID has existed. 03:44.890 --> 03:46.990 And now I'll go right in here. 03:46.990 --> 03:52.000 And I'm going to change this to students underscore ID. 03:52.360 --> 03:57.940 And if I do that I'm now going to create a new or a set in new rule. 03:58.060 --> 03:59.830 But I'm going to affect this. 03:59.830 --> 04:06.460 So go right in here and say that this is going to be Steve at gmail. 04:06.790 --> 04:10.240 And now let's go ahead and execute this query and check it out. 04:10.750 --> 04:12.610 Drag this down. 04:13.330 --> 04:16.720 Now you can see that ID 1014. 04:16.750 --> 04:27.010 Judge Steve has entirely changed from justice at Macomb to justice at gmail.com, and that is applicable 04:27.010 --> 04:28.420 to every other one right here. 04:28.450 --> 04:30.880 So let me go ahead and say I have this ID too. 04:30.880 --> 04:34.510 And I'm going to change this from to ID two. 04:34.540 --> 04:39.100 And let me say the processing, which is a ruse and that is a very fast name. 04:39.100 --> 04:40.120 I'm going to put that here. 04:40.210 --> 04:43.360 And the name is, uh, Mayweather. 04:43.390 --> 04:47.110 That is the last name and went to the place that and the. 04:47.140 --> 04:51.730 Now the email is no and no. 04:51.760 --> 04:53.800 It also updated the email. 04:53.830 --> 04:54.430 Okay. 04:54.460 --> 05:01.600 Now let's go ahead and say the email because it doesn't have an email but it has come back to an email. 05:01.630 --> 05:09.310 But I'm going to say the email address is going to be, uh, Rose Rose Mayer at gmail. 05:09.310 --> 05:19.030 And let me say it's going to be female and everything remains just the same. 05:19.940 --> 05:27.110 And now I'm changing both the email and I'm changing the gender as well. 05:27.140 --> 05:33.650 So if I want to affect this gender or the gender is already female, I can change the gender to know 05:33.680 --> 05:34.040 what. 05:34.070 --> 05:35.870 Let's take that one step at a time. 05:35.870 --> 05:43.760 Now, what I need to do is to go ahead and set the set the email and exclude that. 05:43.760 --> 05:46.400 And then this is going to be updated. 05:46.400 --> 05:48.560 This email is the no. 05:48.590 --> 05:50.000 Which is a 16 year. 05:50.030 --> 05:55.610 What excluded email is the new email we added and if required is the query. 05:55.640 --> 05:56.390 Check it out. 05:56.420 --> 06:00.800 Now we have this and it moved down at the last one. 06:01.790 --> 06:02.900 Check it out. 06:03.170 --> 06:04.250 And uh. 06:06.560 --> 06:08.750 Let's go over here. 06:11.090 --> 06:12.830 And check it out. 06:14.990 --> 06:18.080 So we can also use this to check out the students. 06:18.080 --> 06:21.470 Select all from students. 06:21.500 --> 06:29.960 Where Students underscore id is equal to two and put a semicolon at the end. 06:29.990 --> 06:31.580 And let's go ahead and check it out. 06:31.610 --> 06:34.970 Now you can see that we have a Rosie. 06:34.970 --> 06:38.540 And the new email address is a rosemary. 06:38.540 --> 06:40.130 And it is female. 06:40.160 --> 06:45.020 So because I have large data is sometimes you can be able to see where this actually entered. 06:45.020 --> 06:49.370 And that is why I have to select that and identify this. 06:49.370 --> 06:56.450 So you can see that you can actually update and using what we call the score and check it out again 06:57.800 --> 07:02.870 using what we call a no conflicts do updates. 07:03.050 --> 07:07.160 So we now set this and you can also set multiple. 07:07.190 --> 07:11.180 Let me want to say I want to set what the gender let's say gender. 07:11.210 --> 07:13.160 Or I need to just put a comma. 07:13.160 --> 07:17.540 And I'm going to say gender is equal to. 07:20.570 --> 07:25.970 Escalate that escalated to gender. 07:26.330 --> 07:28.590 So you can add onboard a first name. 07:28.590 --> 07:36.420 You can upload the last name and let me go ahead and also add maybe the first name first. 07:36.450 --> 07:40.770 I can just go over to the next line with a comma, and let's move on to the next line. 07:40.770 --> 07:44.490 First underscore name is equal to. 07:47.190 --> 07:51.060 Excluded dot first underscore name. 07:52.620 --> 07:54.930 First underscore name. 07:55.110 --> 07:58.440 And now we're going to change the first name. 07:58.440 --> 08:03.990 And the first name is Rosalyn I'm going to change this to. 08:08.250 --> 08:09.390 Let me change this. 08:09.390 --> 08:11.700 And I want to put the Rosa at the end. 08:11.700 --> 08:13.050 So can we want to see it. 08:13.050 --> 08:16.080 And this is a waiter. 08:16.290 --> 08:18.480 And I'm going to add okay. 08:18.510 --> 08:21.900 We didn't actually specify the last this uh last name. 08:21.900 --> 08:25.290 And the next one we changed is the gender. 08:25.290 --> 08:28.470 So I'm going to change this gender to male. 08:29.220 --> 08:29.580 All right. 08:29.610 --> 08:33.300 Now we can go ahead Whitehead and R is the cutest query. 08:33.300 --> 08:42.090 And now we see that the first name has changed to Lind Rosa and the gender has changed from female to 08:42.120 --> 08:42.570 male. 08:42.570 --> 08:47.880 So that is what the Akaflieg to do update does for you. 08:47.910 --> 08:48.870 I hope that is cool. 08:48.870 --> 08:54.750 So you can also use a conflict, do nothing and you can also use on conflict. 08:54.780 --> 08:57.660 Do update to do nothing actually doesn't do anything. 08:57.660 --> 09:04.770 What to do update actually updates the table without having an error, because someone can actually 09:04.770 --> 09:10.920 come back to your table and say, I want to update this table having the same ID without having any 09:10.920 --> 09:11.490 problem. 09:11.490 --> 09:15.660 Whenever you use no on conflict, do update. 09:15.690 --> 09:16.770 Hope that is cool. 09:16.800 --> 09:18.930 Go ahead and check it out and practice with it. 09:18.960 --> 09:20.130 If you have any question. 09:20.160 --> 09:25.110 Go ahead and use the question and answer section, and I'm going to get back to you as soon as possible. 09:25.770 --> 09:26.520 Thank you so much. 09:26.520 --> 09:29.340 And I'm going to see you in the next video lecture.