SQL UPDATE syntax explained

This article will get you familiar with the SQL UPDATE syntax and demonstrate how this statement can be used for modifying data using T-SQL. Data modification side of DML language in T-SQL includes three statements used for modifying data in SQL Server and those are: INSERT, UPDATE, and DELETE. The focus here will be on the UPDATE statement explicitly.

SQL UPDATE syntax

So, to start with the definition, the UPDATE statement changes existing data in a table or view in SQL Server. Below is the full syntax for SQL Server and Azure SQL Database:

[ WITH <common_table_expression> [...n] ]

UPDATE

[ TOP ( expression ) [ PERCENT ] ]

{ { table_alias | <object> | rowset_function_limited

[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]

}

| @table_variable

}

SET

{ column_name = { expression | DEFAULT | NULL }

| { udt_column_name.{ { property_name = expression

| field_name = expression }

| method_name ( argument [ ,...n ] )

}

| column_name { .WRITE ( expression , @Offset , @Length ) }

| @variable = expression

| @variable = column = expression

| column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression

| @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression

| @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression

} [ ,...n ]

[ <OUTPUT Clause> ]

[ FROM{ <table_source> } [ ,...n ] ]

[ WHERE { <search_condition>

| { [ CURRENT OF

{ { [ GLOBAL ] cursor_name }

| cursor_variable_name

}

]

}

]

[ OPTION ( <query_hint> [ ,...n ] ) ]

[ ; ]

<object> ::=

{

[ server_name . database_name . schema_name .

| database_name .[ schema_name ] .

| schema_name .

]

table_or_view_name}

Don’t let the syntax scare you. We will be looking at the UPDATE statement using the minimum required syntax. The basic SQL UPDATE syntax comes down to using keyword UPDATE followed by the name of our object (table or table alias) and the SET column name equals to some values.

The FROM clause will come into play when we do joins and we can also have a WHERE clause when we need to update only a portion of data in a table. It goes without saying that using a WHERE in a statement is always a good idea or you might find yourself in a situation of updating every single record in a table.

For the syntax of Azure Synapse Analysis (formerly SQL Data Warehouse) and Parallel Data Warehouse, please refer to official MS documentation: UPDATE (Transact-SQL)

The official documentation is a treasure trove of the UPDATE statement that will take you about 40 minutes to read but has everything that you need to know in one place.

Modifying data using the UPDATE statement

In this section, we’ll be modifying data in tables from the AdventureWorks2014 sample database. However, before we start with a simple UPDATE statement, execute a quick-select from Product table:

USE AdventureWorks2014;

SELECT *

FROM Production.Product p;

The query returns all records from the Product table of items that are sold or used in the manufacturing of sold products. In this table, we have a column MakeFlag that can be either 0 or 1 as shown below:

0 = Product is purchased

1 = Product is manufactured in-house

Simple UPDATE statement

Let’s now run a simple UPDATE statement following the SQL UPDATE syntax described earlier:

UPDATE Production.Product

SET

Production.Product.MakeFlag = 1

WHERE Production.Product.ProductID = 4;

After the execution, 1 row affected message will be returned indicating that the statement went through:

Before the query was executed, the Headset Ball Bearings product was purchased but now it’s manufactured in-house:

Update multiple rows

Let’s move on and look at another example to see how we can update multiple rows at once. This time, we’ll also use expressions from SQL UPDATE syntax which is a really handy way of setting a column equals to itself AKA doing something to a column on itself.

Use the query below to see what we have in the Employee table:

SELECT *

FROM HumanResources.Employee e

ORDER BY e.VacationHours DESC;

The Employee table has information such as salary, department, title, etc. but we’re interested in the number of available vacation hours:

We have a lot of different departments and job titles in the sample database, so let’s run another query and filter out some of the results by saying fetch everything where the job title is e.g. Quality Assurance Technician:

SELECT *

FROM HumanResources.Employee e

WHERE e.JobTitle = 'Quality Assurance Technician';

Here we can see that we have four people with this job title and their available vacation hours:

Let’s just say that those guys have been performing really great for the past three months and we want to reward them by giving them a 20% increase in vacation hours. To do that, execute the query below:

UPDATE HumanResources.Employee

SET

HumanResources.Employee.VacationHours = HumanResources.Employee.VacationHours * 1.2

WHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';

We should see a message that 4 rows are affected meaning records for those four people from the QA department have been updated:

We can check how this update reflected vacation hours by re-executing the quick-select from the Employee table. You’ll notice that vacation hours are increased by 20 percent:

Now, there’s another SQL UPDATE syntax that we can use to get the exact same result. Instead of saying column name = column name, we could use the expression which looks a little better, also more meaningful for some, as shown below:

UPDATE HumanResources.Employee

SET

HumanResources.Employee.VacationHours *= 1.2

WHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';

This is just another way to use the SET statement from the SQL UPDATE syntax and specify the list of columns or variable names to be updated.

It goes without saying that you can perform arithmetic operators like addition(+), subtraction(-), multiplication(*) and division(/) on all numeric operands involved. For example, if you would like to undo changes and take that 20 percent back from the previous example, well then just execute the following:

UPDATE HumanResources.Employee

SET

HumanResources.Employee.VacationHours /= 1.2

WHERE HumanResources.Employee.JobTitle = 'Quality Assurance Technician';

Update data using JOIN

After going through some basics, let’s see SQL UPDATE syntax on how to do updates based on joins. This can be a really neat thing to do because a lot of times when updating data in a table we need data from another table to make desitions on what you’re updating.

This can be tricky at first compared to joining stuff in a SELECT statement and it’s not always straightforward, but once you get familiar with the syntax it gets easier.

With that in mind, let’s forget the existing data in our sample database for a moment and see how to do updates using a join as simple as it gets. For this, we’ll need two new tables. Paste the code from below in the query editor and hit Execute:

CREATE TABLE dbo.Bank

(ID INT NOT NULL,

BankName NVARCHAR(255) NULL,

City NVARCHAR(255) NULL,

SwiftCode NVARCHAR(255) NULL,

CONSTRAINT PK_Bank PRIMARY KEY CLUSTERED(ID ASC)

);

CREATE TABLE dbo.SwiftCode

(ID INT NOT NULL,

BankID INT NOT NULL,

Code NVARCHAR(11) NULL,

CONSTRAINT PK_SwiftCode PRIMARY KEY CLUSTERED(ID ASC)

);

We just created two empty tables on the dbo schema; Bank and SwiftCode:

I entered just a few records in both tables and if we do a quick-select from both tables, here’s how it looks:

As can be seen above, we’re missing the SwiftCode information from the Bank table. We have this information within the SwiftCode table, we just need to join those two tables using BankID and update the Bank table using the information from another table.

So, let’s look at the SQL UPDATE syntax below for achieving this:

UPDATE b

SET

b.SwiftCode = sc.Code

FROM Bank b

INNER JOIN SwiftCode sc ON sc.BankID = b.id;

Once executed, you should see a message that 4 rows are affected by this action:

This means that we just updated the table by using data from another table. Cool, right? If we query the Bank table one more time, here’s what we should have:

When working on a more complex query, the rule of thumb is to always write a SELECT statement first and to just join tables together. Why? Because when you use an UPDATE, everything after the FROM is the exact same. Furthermore, it’s highly advisable to use aliases or otherwise things can get a little funky, especially on complex queries no matter how familiar you’re with the SQL UPDATE syntax.

If you’re new to SQL and don’t quite understand what an alias is, by definition, aliases are temporary names to objects, so they’re easier to work with when writing and reading code. I, personally, use ApexSQL Complete which does this for me by automatically creating aliases to SQL tables and views with it’s Auto-generate aliases feature.

Conclusion

The UPDATE statement is one of the three big statements of the DML side of the T-SQL language that deals with data modification. By the end of reading this article, hopefully, you got familiar with the SQL UPDATE syntax; we saw some basics of the statement, and how we can do an update based on joins which is a very popular and common thing to do.

I hope this article has been informative for you and I thank you for reading it.

Author
Recent Posts

Bojan Petrovic

Bojan aka “Boksi”, an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology, is a software analyst with experience in quality assurance, software support, product evangelism, and user engagement.

He has written extensively on both the SQL Shack and the ApexSQL Solution Center, on topics ranging from client technologies like 4K resolution and theming, error handling to index strategies, and performance monitoring.

Bojan works at ApexSQL in Nis, Serbia as an integral part of the team focusing on designing, developing, and testing the next generation of database tools including MySQL and SQL Server, and both stand-alone tools and integrations into Visual Studio, SSMS, and VSCode.

See more about Bojan at LinkedIn

View all posts by Bojan Petrovic

Latest posts by Bojan Petrovic (see all)

Visual Studio Code for MySQL and MariaDB development - August 13, 2020
SQL UPDATE syntax explained - July 10, 2020
CREATE VIEW SQL: Working with indexed views in SQL Server - March 24, 2020