Join Adam Wilbert for an in-depth discussion in this video Updating data, part of Database Foundations: Core Concepts.
- In order to modify information in a data table, we'll use the dml keyword, update. Let's take a look at a couple of examples based around our employees table. First, I'm gonna right-click on the employees table and choose "Select top 1,000 rows". Inside of the employees table, what I wanna do is change the position of the CEO to read the full text of Chief Executive Officer. I can do that with an update statement. Let's go ahead and go into a new query here and we'll start typing. First, I wanna specify what database we're gonna use. So, I'm gonna type in the use clause and then an open square bracket and then H+active here.
Then, we'll start our update. I wanna update the table of employees. Then, I wanna set the values here of the position field, which is the name of the column to the value of and I'll type in the equal sign to specify that and then, in single quotes, the text that I want it to change to, Chief Executive Officer. Now, if I were to run this query right now, it's gonna go through the table and change everybody's position to Chief Executive Officer. Now, I don't wanna do that. So, what I wanna do is make sure that I type in a where clause so that I target this change to one specific record.
So, I'm gonna type in where, the position field is currently equal to CEO. Let's go ahead and run this query by pressing the execute button and it'll say one row is affected. Now, we can go in and type in a select statement. I'll type SELECT star, from employees. And this time, I'm gonna highlight this just so I run just this bit of code here, these two lines and we'll execute that to return to the employees table and we can see, sure enough, the first person has been changed to Chief Executive Officer here.
If we omit the where clause, then the updates are going to apply to every record. This usually isn't what you would want, but here and there, it has its uses. For instance, if you create a brand new column and you wanna set everyone to the same beginning state, then you could omit the where clause in that case. Let's go ahead and change this up a little bit. Let's come down here below this from line and I'm gonna say alter table and I'm gonna alter the employees table and we're going to add a new column here called benefits. And that's gonna be a character data type and it's gonna have just three characters.
So basically, it's just gonna store a yes or a no on whether they're enrolled in the benefits program with the company. So, let's go ahead and run these two lines by highlighting them and executing and it says it was completed successfully and now, we can run this SELECT statement again. I'll highlight that and execute to see that sure enough, we have a new column here called benefits, but it doesn't have any information for all of our employees. Now, let's go ahead and edit our update statement. I'm gonna update the employees. This time, I'm gonna set the position here or the benefits, excuse me, I'm gonna set the benefits to the value of and I'm gonna type in single quotes here, yes, and we'll just automatically enroll everybody in the benefits program.
I'm gonna get rid of the where line because I want this to apply to everybody here and this is our finished statement. So, we're gonna update the employees and we're gonna set benefits equal to yes. Let's go ahead and execute those two lines by highlighting them. It says nine rows were affected and now, if I run the select statement again, we can see that everybody's benefits has been changed to yes. So, by updating a column of data immediately after creating it, you've essentially set every record to the same starting position. But, by adding the where clause, we can target specific changes to just the records we wanna modify.
Incidentally, you might notice that the word benefits is being underlined in the query. That's because the column was just created and it hasn't been added to the IntelliSense code hinting cache yet. It won't affect the performance of your query, but if you wanna refresh the cache, just hit that shortcut key, control, shift, r.
Note: This course will also prepare certification candidates for the Microsoft Technology Associate Exam 98-364, Database Administration Fundamentals.
- Understanding flat file, hierarchical, and relational databases
- Installing SQL Server 2014 Express
- Creating your first database and tables
- Creating and editing database objects
- Writing commands in DML
- Selecting and inserting data
- Updating and deleting data
- Establishing relationships and file naming conventions