Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Well just about as often as inserting information you're going to need to update information too. Let say the example that I was looking at, I know that I've got a problem with one of these rows. This last row here a ProductCategoryID 55 should have had a ParentProductCategoryID of 1 instead of 2. So let's see how to go ahead and do a simple example like that. We will use the word UPDATE and we're going to pick the table name, which is in this case SalesLT.ProductCategory. And just as SELECT has a FROM and INSERT has INTO, UPDATE has a SET.
We're going to SET. We want to say what the values that we want to change are. In this case it was actually ParentProductCategoryID. IntelliSense is smart enough to know that if we're working with the ProductCategory table these are the only options that we have. And then I can say and in this case I'm going to do quite a specific change SET ParentProductCategoryID equal to 1. But if I were to run this code right now it would update every single row in the ProductCategory table and set back column to one, which is certainly not what we want.
So when you're doing an UPDATE, you tie a WHERE clause to it. Exactly like a SELECT statement. In fact more important than when you're doing a SELECT statement. So I want to say well, WHERE? Under what conditions do I want to do this? Again the example that I shown was that we had an incorrect one right at the end here, which was ProductCategoryID equal to 55. Now I could use any of the same WHERE clauses that I used in the select statement but that's the easy one. So I'll say WHERE ProductCategoryID equal to 55 and let's execute that.
One row affected. Well right now just like doing the INSERT the UPDATE doesn't tell you anything about what it did. It can but not this using a plain old update statement like this, and this would be something that we could theoretically execute again and again and again because it's just going to do the same thing. Not that I recommend it, but it should work and now if I go and take a look at that ProductCategory table we should find that yes, indeed the ParentProductCategoryID has been set to 1. But do bear in mind that whatever you put in the WHERE statement, whether it's a range or whether you miss it out entirely, if you had retrieved multiple values, if you put that WHERE in the select, you would update multiple values if that's in an UPDATE.
Now what about if I wanted to update multiple columns in that one row? Well that's pretty easy. So I just have that after the SET. We're setting the first column here ParentProductCategoryID equal to 1, Name equal to, for example, Commuter Bikes and we just execute that. One row affected. Now this is the most you're going to get right now of the information about what actually happened.
Obviously you can do a little bit more than that. Let's say for example I update a different table. I'm going to update SalesLT.Product and I'm going to set the list price. Let's say we've had a conversation, which is we want to increase our prices by two dollars where our prices are less than 50. Well I'm going to say SET ListPrice equal to. Now how do I do this? Well very easy. I just say ListPrice equal to whatever ListPrice is +2 WHERE ListPrice < 50. Execute and we see on this 49 rows affected. Your number may be slightly different if you're following along.
Now if you're constructing an UPDATE statement and you again a little nervous about how many rows you might affect, do bear mind that the WHERE clause is what you want to look at and before you run your UPDATE statement you could, if you're worried, about it just say SELECT *FROM SalesLT.Product, keeping the same WHERE clause, and figure out well how many is that going to actually change? In this case it is telling me 49 rows. Now I might have had a slightly different result because I've already executed my update but this would give you the verification of the update.
It's going to update as many things as you expect. Like everything else in SQL Server you can get a little deeper than this. I recommend that you check out some of the available options that you will see for the UPDATE statement on SQL Server Books Online but that's the core of how to do an UPDATE statement.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 81393 Viewers
80 Video lessons · 133275 Viewers
52 Video lessons · 66638 Viewers
59 Video lessons · 52459 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.