3.3 Update Query Basics

Now that we have some data in our database, it's time to go ahead and begin to learn how to update that data. And what we're going to do or what I'm gonna do in this lesson, is I'm gonna show you the very basics of updating data in a database in a table and point out a couple things that you need to be aware of before you start modifying data. So it's one thing to select data out of a table and view it and see what's in there. But as soon as you start modifying data, whether it's an update or a delete, you really have to start paying close attention to what you're doing, because if you don't do things correctly, you can cause catastrophic problems. And believe me, almost all of us that have ever worked with databases ever in our lives have screwed something up, it's just that easy. So let's talk about some of the basics here. So now I wanna talk about updating some records, so we're gonna use an update query. Now in order to do this, I need to specify what table I wanna update data in. So I'm gonna say update guest, and then I'm gonna drop down to the second line, and now I need to set a value for a particular column. So I'm gonna use this set keyword, and now I need to specify what I need to set. So what in this data do I wanna set? Well, let's say that I was walking down the street and I ran into Jane. And I either asked her her last name, or maybe I saw her last name on the mail box. And I know that it is Doe, so I need to update her last name to be Doe. So, the way that we're gonna do this then, is we're gonna specify what column do we wanna update. So, we'll say last_name a nd we do wanna set that equal to her last name being Doe. Now at this point, I could go ahead and put in a semi colon and hit Enter, and you're gonna see something very curious. You're gonna see that the query was okay, but I see two rows affected. But why are there two rows effected? I only really wanted to update one. So let's go ahead and do a select * from guest, and take a look and see what happened. Well, you can already see that we have a bit of a problem here. Now instead of just updating Jane's last name, I actually updated everyone's last name because my update statement was very broad and said I wanna update the guest table and I want to set last_name equal to Doe. Well, because there's no filtering on there like in the last lesson we talked about adding a where clause, we didn't do anything like that. So, unfortunately, now we updated every single record's last name column to be Doe. So imagine if you had a million records in here and you were throwing a huge party. Now everybody's last name is Doe and that's a huge problem. That's one thing you don't ever ever ever wanna do. You always wanna make sure that you are being very specific about what you're trying to modify in your table using some unique value, and do we have a unique value here? Well, we do happen to have a unique value of ID because we specified it as the primary key. So let's start to fix our data here a little bit. So I know that Derek's last name is not Doe, it's actually Jensen, so now I want to update this back to Jensen. Now, let's go ahead and begin to write our update statement again. So I wanna update guest, and I wanna set last_name equal to Jensen this time. But I don't wanna just blanket everything and update everything to be Jensen. And I wanna use my where clause again just like I did in my select queries, but this time I wanna narrow down what it is I want to update. So, how do I narrow it down to be just this one? Well, I could say first name equal to Derek. But what if this is a really big table with a lot of Dereks in there, and then we're gonna update a bunch more records that I maybe didn't need to. So in this case, I wanna be very specific, I only wanna update this one record to have a last name of Doe. And the way that we're gonna do that is by using our unique identifier. So we wanna see where id = 1, so now I'm narrowing down the scope of what it is I wanna change in my table, so let's go ahead and hit Enter. Now we'll see that only one row was affected, so that's good. So let's go ahead and pull back everything so we see now that this is looking a little bit better, and we're back to something a little bit cleaner. We now have Derek Jensen, Jane Doe, Derek is coming and Jane is not. So now we see the basic process of updating data in a database. Now, you can also do multiple columns at the same time. So let's go ahead and clear this out, and let's bring back everything from guest. And let's say that I needed to update both the last name and the confirmed value. So maybe D-O-E is not the way that you spell her last name, maybe it's just D-O, or maybe it's actually something else, maybe it is some other last name. How can I do multiple updates in one statement? Well, we're gonna say update guest again. And in this case I wanna set last_name equal to and I wanna set that equal to D-O-H, maybe, as in Doh, and then I also wanna set confirmed equal to yes. Because she actually is going now and now I'm going to specify where id = 2. So now I can comma separate the values that I want to update or the columns that I want to update in this case last names and confirmed where an id = 2. So let's go ahead and hit Enter, we have one row affected. Let's go ahead and go back to our select *, and as you can see now I have updated both the last name and confirmed. So I don't have to do multiple updates for each individual column, I can chain them all together with commas, and update multiple columns in a single row.