Hey guys, in this video, we'll be looking at how we go about changing data that already exists. Now the first comments is that we would like to update all enrollment dates that are currently empty. And this is a perfect testbed for us to write or first UPDATE statement. So our key word for updating a record or changing a record is literally the word update. And before anything else, we have to ensure that we're in the cart database table. So we'll just change that from Master to school to ensure that when we run this update command, it actually looks at the correct table in the correct database.

So once again, our key word here is update, and then we specify the table. In this case, we want to change the enrollment dates, and we know that we have enrollment dates in our students table. So we have to specify students and then the next key word is set after We specify the column or columns and the value that we would like to put in each column. So we have updates the table students set and the column that we would like to update is enrollment dates. So we specify that and then we specify the value that we would like it to be. So we would like to specify that all of the enrollment dates that are no get updated to 2019 dash 05 dash 01.

Now, as a quick recap, we can just take a quick look at our table and we see that we have quite a few low records. So at the end of this only the records that are no should be updated, meaning we should still see these values unchanged. No, you need to have a condition that will specify which rules will get affected. So our condition is where in the enrollment deeds is Empty are no. So we see over here that we have seven records that should get affected by this query. So we need to add a condition using our where statement just like from our select, and then we will say the column enrollment dates.

And because we're dealing with know, you probably would want to write equals No, and that would not throw an error. But what it would do is bring by zeros effective, because when we're checking for knows, we need to say is no. And on the flip side, you would say is not no meaning you want any column that is not currently empty. So that's one of those little gotchas that might pop up during your your experimentation. And if you haven't run into it before, no, you know, if you have before, no, here you have solution. So we say where this column is, no, we want to update the same column with that value, and then we can go about it.

Executing this and then we will see the seven rules have been affected. So if we go back to our SELECT statement and we just execute it once more to see or updates, we see that there is no more any no record or no enrollment date in our students table, and that is our first update statements. Alright, so task one is completed. And the next task would ask us to update all enrollment dates. And then I left a little warning here that we would be updating multiple records and you should be careful and I'm going to explain why. Now we can replicate this code and I'll just retype it UPDATE students set enrollment dates, which is the column that we said we are going to be updating and we're going to set all enrollment dates to one specific value.

So 2019 and let's try 06 dash zero, or Let's try one, zero. So we're updating all of the student records to see that everybody in this table as at the time the square was run was enrolled the 10th of June 2019. The reason I said be careful is that we don't have any condition on this statement, which means that this statement will be executed against every single record. And I can tell you that if you miss type, or you make a mistake, and we're all human, but you may make a mistake and write this and you want it for specific roles, and you leave off the condition then this will be executed against every single record, which may not necessarily be desirable. So as a database administrator or developer, you want to be very careful and deliberate when you're running your update statements and ensure that this statement, if it doesn't have a condition, it is perfectly fine.

And if you only want specific rows, you ensure that you Put on your condition. So for this example, we want to update everybody's enrollment dates. So the 10th of June. So we'll just do that execute, and we see that we have 11 rows affected. So if we go back and look at the updates, then we will see that everybody now has the 10th of June as their enrollment date. And that was our desired results.

Once again, if you want to update specific rules, you make sure that you include your condition. And now on to our final task for today's video, we will be updating the students with IDs seven and 11. Now at the start of this course, when we were creating our tables out I've said quite a few times that each record should be uniquely identifiable. And what makes each record uniquely identifiable is the ID that is given. So we implement entered an auto incrementing ID column so that every time I record goes in even if all the other information is the same, this ID number or value would be different. And so this will allow us to specifically target any record in our database regardless of what is in the other columns.

So in this situation, we want to make an update to the names for students seven, and 11. So if you don't want to identify them by name, because maybe they have the same name, it's hard when you have two students with the same name, you're at a loss for which one should specifically get updated. So by using our ID column, we can know that even though we have students and we have students one is seven and one is 11. So I can target seven and make a change and I can target 11 and make a change. So let us execute those and another thing that we will be doing is changing their first and last name. So we will be exploring how you go about making an update to multiple columns using one update statements.

So we would say update or table students. And we want to set first name be equal to the G and last name to be equal to Mach Vera mocks. So here we have two column and value pairs, we have the first column and the value that we're setting and the second column and the value that we're setting, so as many columns as you need to make an update, so you can just comma separate them and chain them along. And you do all of that up until you get to your condition, which is where and in this situation, we want to update the students where the ID is equal to seven. And once again, remember that ID is a number column. So the value of would not be in quotation marks because it's just a number.

So if you're dealing with a VAR char and var char or char anything with words are alphanumeric characters, then you would use your single quotation marks. But then when you're dealing with a column that is not a text based data type, then you don't need your quotation marks. So when we execute this, the expectation is that the students with the ID value being seven is going to get changed is going to get the first name and the last name changed. So we'll just execute and we see we had one row affected so we can go back and just re execute or select and take a look at student ID seven from now that student to test two when we execute, we see that student seven is no to G marker amount as expected. So we can just go ahead and do the same thing for the next students.

We would like to update which is student 11. And we're changing This students to karate song. So when we execute this, then we should see students with ID number 11 get updated in the system, one row affected and we look again, that was student six, test six. When we execute again, we see that we know have Shawn and Ronnie. So that is how you go about making an update via script. You can scroll to the top of this file, which is included in the notes for this lesson.

And I gave you some other names that you could use to update your student records just to give you some practice. And you can also play around with updating other data values and setting up other conditions that would specify which records get updated.