Bojan Petrovic
Results grid showing before and after values of the Vacation Hours column

SQL UPDATE syntax explained

July 10, 2020 by

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:

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:

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:

An executed SELECT statement highlighting Make Flag column in the results grid

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:

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

An executed UPDATE statement with simple SQL UPDATE syntax showing that 1 row was affected

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

Results grid with highlighted Headset Ball Bearings product and its Make Flag value

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:

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

An executed SELECT statement highlighting Vacation Hours column in the results grid

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:

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

An executed SELECT statement highlighting Vacation Hours and Job Title columns in the results grid

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:

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

An executed UPDATE statement with SQL UPDATE syntax showing that 4 rows were affected

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:

Results grid showing before and after values of the Vacation Hours column

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:

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 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:

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

Successfully executed script for creating the Bank and Swift Code tables

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

Successfully executed script for retrieving data from the Bank and Swift Code tables

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:

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

Successfully executed script using SQL UPDATE syntax for updating a table using data from another table

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:

Results grid showing before and after values of the Swift Code column

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.

Bojan Petrovic
DML, SQL commands, T-SQL

About Bojan Petrovic

Experienced QA Engineer with a demonstrated history of working in the computer software industry. Skilled in network technologies, technical support, Windows SQL Server, etc. Strong information technology professional with an AP graduate in IT Technology focused on Networks and electronic technology from the Copenhagen School of Design and Technology.

1,968 Views