Hey guys, welcome back. In this lesson, we will look at how we can go about updating data that is already in our database. Now if we take a look at some of the data that is already in our database, so far, we have been populating only the students table. So I can just go ahead and run that select query against a students table, and we can take a look at what is in there. And we see that we have a lot of students called test. And so we want to probably start changing some of the values associated with these students.

And so what we want to do is look at how we go about writing SQL statements that would effect these changes. Now back in our empty script file, we will start looking at a new keyword and this keyword is update. So it will say update. And then the keyword update is followed by the table that we're about to effect some update command on in this case, it would be students, so I already have my use school DB here. So I don't have to say just school DB dot students again So I just say students, and then we have another keyword which is set. And then after we say update the table name and the keyword set, what we will have is the values that we wish to change.

So we will have a column and value pair. Now let's say that we wanted to update the enrollment date for test students because they finally have enrolled into the course and that would have happened 2019 0701. So we need to change the enrollment date for to test student to that date. So what we will do is say specify the column enrollment on the score date. And then we give it a value, which in this case would be 2019. So this is the value that we wish to change tool, and then we could execute but please do not execute just yet.

Because what happens is that if you run this statement as is, what it will do is literally go to every student's record in this table and make this update, which is not what we desire, we want to specifically update this records enrollment date. So once again, be very, very careful when running this update command because you have to specify if needs be, which record it is that you wish to carry out this update on. If you have multiple columns that you wish to change the values for then you could always just comma separate them. And you continue with that same column and value pair. So if I wanted to change the last name of test students, maybe there was a typo, and we spelled it wrong. So we want to just update the name of the student and the enrollment date.

Then we can just specify as many columns as we wish so last name would know be McDermott and first name would be Wilma. So I'm just showing that one, you can specify as many columns as you wish to be updated. And I'm just breaking the line. So we can see here. And then just to drive home the point, you need to specify which one of the records you wish to update before you run this command. So in this case, because we're targeting test students, we have a number of options here.

When we're when to apply that specification. We could either try to target based on the first name, but then that or the last name, but then that would not be very clean. Because if we have more than one persons with the same name, because it's a school, we could have more than one persons named john brown for argument's sake. So if you were to try and target the students based on the first name, or the last name, or even both, you could still end up targeting more than one records and in erroneously saw so that is one of the key reasons that we always try to have a primary key value on each rule, because we if we target the rule based on this value, then we can be certain that we are targeting this specific record. And so to apply this specification or filter, and I was holding on using that word, because we just wrapped up a lecture on using filters when we're selecting, well, the same mechanism that helps us to filter on a select actually helps us to filter or specify which record we wish to carry out and update command on.

So we can reuse or we're keyword and specify the condition and the condition that I would like to meet to in order to fulfill this update command would be where the ID of the student is the value one. Remember that we're targeting test students. So the easiest way to know now we're targeting to students is just specify where ID is equal to one So I will just say where ID is equal to one, and then end it with a semicolon. And then if I execute this and just execute this entire script, we see here that we had one rule affected. And it even tells us that this kind of effect was a change. All right?

So if I go back to my query looking for my students, and I re execute this query, then I will see that students with ID one no has better information, Vermont's Wilma, and the enrollment date. All right. So once again, if I failed to specify which record it is I wish to update, then this update command will actually go through the entire data set. So be very, very careful and deliberate when running this. This will be similar to your drop commands, be very, very careful and deliberate when you're about to execute an update statements. So you want to make sure that you are targeting exactly who it is.

You wish to update. Now for virtually everything that you can accomplish with a command, at least at this basic, create, read, update and delete or crud level, you can actually replicate that using just the interface. So we just wrote a command that handles updates, and I'll save this script and include it with the resources for this video. But we can also having selected our records and gotten our results here, apply changes right here in this grid. So if I wanted to update student with ID number seven's on enrollment dates, then I could just double click in that space and change this value. So 2019 dash 04 dash 26 just putting in a random date there.

All right, we see that it is now applied. And then if I of course need to submit this, then I need to click Apply Over here, which we should be used to from our insert, video, and then we just click Apply. And then it will generate a very similar statement to what we just wrote. So update school DB dot students set, and it will give you the column and the value. And then we see where it is even specifying where ID equals seven, and you see that they use a parenthesis here. And you can tell that that's not absolutely necessary because we wrote all those without and it worked.

So like I said, the generated code sometimes has some differences in it, and it's good to see it both ways. Alright, so if we're satisfied that that is exactly what we want to do to review it, and we just click apply and then we see that it has executed the statement successfully. So if I refresh, I can just refresh by which will re execute this query. Then we see the changes stay. Alright. So that is how you go about it.

Updating records in my SQL Server. Alright, so in the script file, I have included some activities here in the comments. So you can when you don't know the script file trying to complete these, I will actually be uploading the script file with the solutions really, truly. So while you're watching this video, you can go ahead and look at the requirements and try and complete them. But then the uploaded script file will have the code that matches these requirements. Alright, so happy hunting and see you next time.