Click here to Skip to main content
15,077,931 members
Articles / Programming Languages / SQL
Technical Blog
Posted 23 Nov 2016

Tagged as

Stats

7K views
2 bookmarked

Introduction to the UPDATE Statement

Rate me:
Please Sign up or sign in to vote.
4.17/5 (4 votes)
23 Nov 2016MIT7 min read
An introduction to the UPDATE statement

The UPDATE statement is used to change data within columns of a SQL Server data table. In this article, we’ll explore how to use the UPDATE statement. We discuss some best practices, limitations, and wrap-up with several examples.
This is the third article in a series of articles. You can start at the beginning by reading Introduction to SQL Server Data Modification Statements.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.

Before We Begin

Though this article uses the AdventureWorks database for its examples, I’ve decided to create an example table for use within the database to help better illustrate the examples. You can find the script you’ll need to run here.

Also, let’s initially populate the table with some data using the following INSERT statement:

SQL
WITH topSalesPerson (FullName, SalesLastYear, City, rowguid)
AS (
SELECT S.FirstName + ' ' + S.LastName, 
       S.SalesLastYear,
       S.City ,NEWID()
FROM   Sales.vSalesPerson S
WHERE  S.SalesLastYear > 1000000
)
INSERT INTO esqlSalesPerson (FullName, SalesLastYear, City, rowguid)
SELECT FullName,
       SalesLastYear,
       City,
       rowguid
FROM   topSalesPerson

You can learn more about the INSERT statement by reading our article Introduction to the INSERT Statement.

Basic Structure

The UPDATE statement is used to change column values. Though an update statement can modify columns data from many sources, such as literal values or other query results, the basic format is the same.

There are three components to an UPDATE statement:

  1. The table you wish to change
  2. The column you wish to change
  3. The source data you wish to use to make the change

The general format for the UPDATE statement is:

SQL
UPDATE tableName
SET    column1 = value1,
       column2 = value2,
…

We’re now going to do some sample updates, so if you haven’t done so already, run the script to create the esqlSalesPerson table.

Simple Example – Updating Every Row

In this example, we insert a single row into the esqlSalesPerson table. Here is its table structure:

UPDATE table example

Let’s assume we want to update the city for every sales person to Ann Arbor. The command to run is:

SQL
UPDATE esqlSalesPerson
SET    City = 'Ann Arbor'

You can also update more than one column at a time. To update both the City and rowguid, we can run:

SQL
UPDATE esqlSalesPerson
SET    City = 'Ann Arbor',
       rowguid = NEWID()

Which updates the table to the following:

Simple UPDATE all rows

SQL
UPDATE esqlSalesPerson
SET    FullName = 'Don Sax'
WHERE  SalesPersonID = 10027

Changes Donald Sax’s name to Don Sax.

Notice we used the primary key SalesPersonID to filter the row. This makes it really easy to ensure we are updating exactly one row.

Simple UPDATE example results

Note: The SalesPersonID generated for your table’s rows may be different than what is shown in the exercises, since this primary key is auto generated.

Simple Example – Updating Multiple Rows

The UPDATE statement is capable of updating more than one row. This is controlled by the WHERE clause. All rows returned via the WHERE clause criteria are updated.

Suppose every SalesPerson whose ID is less than 10031 worked in Saline. To update the rows to reflect this, we could use this UPDATE statement:

SQL
UPDATE esqlSalesPerson
SET    City = 'Saline'
WHERE  SalesPersonID < 10031

Which results in the following row modifications:

Simple UPDATE multiple rows.

Considerations using UPDATE

The UPDATE statement is complex and there are many elements to consider. Here are some of the more important ones. For a full list, check out the UPDATE (Transact-SQL) article.

Data Type Considerations (Padding)

Keep in mind that when updating data in columns whose data type is CHAR, VARCHAR, or VARBINARY, the padding or truncation of data depends upon the SET ANSI_PADDING setting.

When SET ANSI_PADDING OFF then CHAR data types are padded with spaces, VARCHAR data types have trailing spaces removed, and VARBINARY have trailing zeros removed.

For instance, if a field is defined as CHAR(10) and you update the value ‘Kris’ into this column, then it will be padded with six spaces. The value is changed to ‘Kris      ‘.

Error Handling

You can handle errors when executing an UPDATE statement using a TRYCATCH construct.

There are several common reasons an UPDATE statement may fail. Some of the common ones are:

  • Unique Key Violation – You’re trying to update a value which causes a duplicate key value.
  • Data Type Conversion – You’re trying to update a value which can’t be correctly converted into the corresponding columns data type.

In these cases, the UPDATE statement execution stops and the UPDATE generates an error. No rows from the UPDATE statement are saved into the table, even those rows that didn’t generate an error.

This “all or nothing” behavior can be modified for arithmetic errors. Consider a divide by zero error.

SQL
UPDATE myNumbers
SET    X = 10/0

Will generate an error if SET ARITHABORT is set to ON. In this case, the inserted is stopped, no rows are updated and an error thrown.

Locking Behavior

An exclusive (X) lock is always placed on a table when an update is run. The lock is held in place until the update statement completes. Since this is an exclusive lock, not other transaction can modify data on this table, until the one which originally placed the lock is completed.

Complex Example – Update From Another Table

In this example, we’re going to show you how to update’s column values based on a match to another table. The general form to use is:

SQL
UPDATE maintable
SET    columnToUpdate = secondtable.columnValue
FROM   maintable
       INNER JOIN secondtable
       ON join condition

Where maintable is the table you wish to update and secondtable contains the update values; we use joins to do the matching.

Let's suppose that someone has accidentally updated all esqlSalesPerson.City column values to NULL!

How can we easily repopulate this data without having to retype it in?

Knowing that esqlSalesPerson was originally populated by information from Sales, we use knowledge to set up a query that pumps data from vSalesPerson into esqlSalesPerson. This is possible since the sales person’s full name is common to both tables.

In the diagram below, you’ll see where there is a match between these two tables:

Relationship between esqlSalesPerson and vSalesPerson

By matching esqlSalesPerson.FullName to vSalesPerson, we can update esqlSalesPerson.City with the matched record.

Before we go much further, let’s first wipe out the city column values. You can run the following to do so and then verify all cities are NULL.

SQL
-- Blank out the City
UPDATE esqlSalesPerson
SET    City = Null;

-- Proof
SELECT SalesPersonID, City
FROM esqlSalesPerson

Once that is complete, we can proceed to update the city with the match value using this UPDATE statement:

SQL
UPDATE esqlSalesPerson
SET    City = v.City
FROM   esqlSalesPerson 
       INNER JOIN Sales.vSalesPerson v
       ON e.FullName = v.FirstName + ' ' + v.LastName

Once you look at the statement, you’ll see we added FROM and INNER JOIN clauses.

Recall the INNER JOIN clause is used to match the rows from one table to another using a join condition.

In our case, the join condition is e.FullName = v.FirstName + ‘ ‘ + v.LastName. Notice here we’re constructing a full name from the views FirstName and LastName columns. It’s cool, and it works!

So, once we have the join, the last bit of business to it do the update. We simply set City to its match counterpart, v.City, and all is well.

Complex Example – Using OUTPUT to Log Changes

The OUTPUT clause is used to log changes made to rows affected by an UPDATE statement.

Suppose the SalesLastYear figures were found to be incorrect and needed to be adjusted by five percent. You can easily adjust the values using the following statement:

SQL
UPDATE esqlSalesPerson
SET    SalesLastYear = SalesLastYear * 1.05

However, suppose the sales department wants a record of all changes. Sure, you could run a query before the update, one after the update, and then compare, but rather than doing all of that, we can use the OUTPUT clause to log the changes directly into a table.

The complete script to run to log the output into a temporary table is:

SQL
DECLARE @UpdateLog table(
SalesPersonID int NOT NULL,
OldSalesLastYear int,
NewSalesLastYear int)

UPDATE esqlSalesPerson
SET    SalesLastYear = SalesLastYear * 1.05
OUTPUT inserted.SalesPersonID,
       deleted.SalesLastYear,
       inserted.SalesLastYear
       INTO @UpdateLog

SELECT SalesPersonID,
       OldSalesLastYear,
       NewSalesLastYear
FROM   @UpdateLog

We’ll not talk about the three pieces in turn.

Notice we’re declaring a table variable to house the logged results. We do this using the following command in our script:

SQL
DECLARE @UpdateLog table(
SalesPersonID int NOT NULL,
OldSalesLastYear int,
NewSalesLastYear int)

This statement defines a table with three columns and names the table variable @UpdateLog. The reason we’re using a table variable is temporary and will be removed once query session is closed.

With the table variable defined, we’re safe log the results using OUTPUT as we do here:

SQL
UPDATE esqlSalesPerson
SET    SalesLastYear = SalesLastYear * 1.05
OUTPUT inserted.SalesPersonID,
       deleted.SalesLastYear,
       inserted.SalesLastYear
       INTO @UpdateLog

As each row is updated, the special column prefix values deleted and inserted provide a means to see the value before and after the update respectively.

The old sales value exists in deleted.SalesLastYear; whereas, inserted.SalesLastYear contains the newly updated value.

To view the logged values, you can run the following:

SQL
SELECT SalesPersonID,
       OldSalesLastYear,
       NewSalesLastYear
FROM   @UpdateLog

which displays:

OUTPUT clause results

Note: If you like the OUTPUT clause, remember, you can also use it with INSERT, DELETE, and MERGE!

The post Introduction to the UPDATE statement appeared first on Essential SQL.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --