Lecture 16-- Write an UPDATE statement
- [Instructor] Using a query, we can update data that's stored in the data tables. This will require the use of a new keyword that we haven't seen yet called Update. First, I want to review the information that's stored in a table here in the Wide World Importers database called Sales.SpecialDeals, and we can do that with a new query window. First I'll type in the keyword Select. Now at this point I don't know the names of the columns that I want to pull out of the table, so I can use a wildcard character, the asterisk, which is the Shift + 8 on your keyboard, and this'll just be a shortcut way of saying just select every column from the table.
We'll come down to the next line and we'll say from, and the name of the table was Sales.SpecialDeals. Let's go ahead and execute this query, and we'll get these results down below. Now this table only has two records currently. The first is a discount that applies to orders placed by a company called Wingtip Toys. We can see in the Deal Description that it's a 10% discount that applies during the first quarter on USB drives for Wingtip Toys. We have the Start Date and the End Date columns for the promotion, and, if I scroll over to the right, we can see the discount percentage is being applied as 10%.
So let's suppose that this discount has been renegotiated and should continue to apply until the end of 2017. We'll need to update the values in the End Date column as well as the Deal Description. However, we only want this update to occur on the first record of the table. If I go back and scroll to the left, we can see that that's the one with the Special Deal ID of number 1. Incidentally, this Special Deal ID is the primary key for this table. Let's come back up here into our top of our query window, and I'll come down a couple more lines, and we'll start our query with the Update keyword.
The first thing we need to say is the table that we want to update, in this case Sales.SpecialDeals. Then, what do we want to update? Well, I'm going to use the Set keyword, and we want to set the End Date equal to the value of 12/31/2017. To type in the date I'll just type in the single quotes and then the 12/31/2017. We'll finish that with a closing single quote. We also want to set another value, so I'll type in a comma here, and we want to set the value of the Deal Description to the text 10% 2017 USB Wingtip.
So those are the two changes that we want to make to our Sales.SpecialDeals table. Now if I were to execute this query right now, it would apply this change to both records in this table. I want to make sure that I'm only targeting the very first record, so we'll come back up here and we'll type in one more line. This is going to be a Where clause. We're going to target this edit to where the Special Deal ID is equal to one. Now we should be all set to make this change. Now if I were to execute the query as is, it's actually going to run this Select statement at the top and then the Update statement.
One shortcut that we have inside of Management Studio is that we can highlight just specific lines that we want to run. I'll go ahead and highlight lines five through seven and execute just those three lines. This time I'm not seeing any results because we're not actually selecting anything from the table, we're just updating the record. So the message down below says that one row was affected by our change. Now let's go ahead and highlight lines one and two and run that Select statement again. Now we can see the Deal Description has the new text that we typed in as well as the updated End Date. So using an Update query is a fast and secure way to modify lots of records in the database.
Rather than searching through large tables to change values one at a time, you can essentially use the power of the T-SQL language to perform highly targeted find-and-replace tasks.
▶️ DTube
▶️ IPFS