Database Fundamentals #15: Modifying Data With T-SQL
Database Fundamentals #15: Modifying Data With T-SQL
Efficiently modifying data in SQL Server is typically done with the use of T-SQL. In this post, we take a look at some examples of how to do just that.
Join the DZone community and get the full member experience.Join For Free
Discover Tarantool's unique features which include powerful stored procedures, SQL support, smart cache, and the speed of 1 million ACID transactions on a single CPU core!
The preferred method for modifying your data within a database is T-SQL. While the last Fundamentals post showed how to use the GUI to get that done, it’s not a very efficient mechanism. T-SQL is efficient.
The command for updating information in your tables is
UPDATE. This command doesn’t work the same way as the
INSERT statement. Instead of listing all the columns that are required — meaning columns that don’t allow for
NULL values — you can pick and choose the individual columns that you want to update. The operation over-writes the information that was stored in the column with new information. In addition to defining the table and columns you want to update, you have to tell SQL Server which rows you’re interested in updating. This introduces the
WHERE clause to the T-SQL statements you’re learning. The
WHERE clause is used in
SELECT statements, as well. You can choose to leave the
WHERE clause off, but then you’ll be updating all values in the table.
As a matter of fact, since all the values for the
CityOfBirth column in the table I’ve been using through these blog posts are currently
NULL, let’s use that as a starting point for the
For the purposes of our exercise, the business has decided that it doesn’t want
NULL values for the
CityOfBirth field. Instead, the business would like to see the value
'Unknown'. This query will make that happen:
UPDATE Personnel.Person SET CityOfBirth = 'Unknown';
If you run this query, all rows will contain the value
'Unknown'. You should have received a message saying some number of rows affected. In my case, it says "6 Row(s) affected."
Note, this updated all six rows in the table, all at once, from a single statement. If you had tried to update these using the GUI, the typing, even if you used copy-and-paste, would be much more time-consuming than the speed with which batch processing can occur using T-SQL.
Above and Beyond
One of the interesting things about an
UPDATE statement is that you can run it over and over again. If you re-run that statement, it’s just going to substitute the same values in the same places. If you were to re-run one of the
INSERT statements you would get a second, identical record — that is, identical except for the
Identity value (if there is one on the table). This is something to keep track of in your systems. There are ways to ensure this doesn’t happen, but they’ll be covered in detail in another blog post.
To update values on individual rows, you have to use the
WHERE clause. With the
WHERE clause, you can target different rows. As an example, the business has determined that they have the
CityOfBirth of the individual with a
PersonID equal to 1. This is how you can modify the
CityOfBirth column for just that one row:
UPDATE Personnel.Person SET CityOfBirth = 'Stockholm' WHERE PersonID = 1;
When you run this query, it will change only the value in any rows within the table that satisfy the conditions of the
WHERE clause. The message returned should read "1 Row(s) affected." In this case, that’s only one row. But it doesn’t have to be. The business has now decided that they didn’t want the values where the
CityOfBirth is not known to read
'Unknown'. Instead, they want them to read
'Unknown'. This gives you the opportunity to learn some other new functionality available through T-SQL and the
UPDATE clause. You can refer to the columns in the
UPDATE statement and use them as part of the functionality. In this case, instead of typing the new, all upper case value, you can use the
UPPER() function from T-SQL to do the typing for you. You just have to refer to the column that you want changed.
UPDATE Personnel.Person SET CityOfBirth = UPPER(CityOfBirth) WHERE CityOfBirth = 'Unknown';
UPPER function and the
WHERE clause in place, the rows affected should have returned a value of 5.
Now, the data is reflecting what the business would like to see with the values formatted the way they want. Once more, this would have been a real pain to perform through the GUI. You need to remember that your databases, for the most part, will have considerably more than six rows. Imagine trying to copy and paste to make these updates on 600 rows, or 60,000. T-SQL quickly begins to prove its worth as you start working with sets of data rather than individual rows.
So far, all the updates have been to individual columns, but this is not how you would normally be performing updates. You would probably be working on multiple columns all at the same time. To change more than one column, you simply add them to the statement. In this example, the business now has both the
GovernmentID and the
CityOfBirth for the person with the
PersonID value equal to 3.
UPDATE Personnel.Person SET CityOfBirth = 'Ghent', GovernmentID = '123-45-6789' WHERE PersonID = 3;
Just separating the columns you do wish to reference by a comma allows you to update multiple columns from a single
Just remember that if you don’t use a
WHERE clause in the
UPDATE statement, you will modify all values in the table. Use due caution when running
UPDATE statements because of the potential scope of this type of problem.
If you’ve been reading these Fundamentals blog posts right along, then you have seen editing using the GUI. It’s just not the right way to get things done. As you see above, T-SQL makes data modification much easier. There’s a whole bunch more you can do with the
UPDATE statement, but we’re going to move on to talk about removing rows.
Published at DZone with permission of Grant Fritchey , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.