This article will help you understand the SQL UPDATE syntax used in SQL Server. Microsoft SQL Server is a database system that is used to store various types of data which is logically arranged in form of tables, columns, and rows. As businesses need changes or new requirements come, we need to modify this data stored in the table. We use the SQL UPDATE syntax to modify or update existing data in a table or view in SQL Server. We can use this statement to modify a single unit of data field as well as multiple sets of data fields based on our requirements.

The syntax of the SQL UPDATE statement is shown below.

We must always be careful while running SQL UPDATE statements. If you have to update a specific data field with conditional expressions, then ensure to not miss the WHERE clause from the UPDATE statement otherwise if you run the UPDATE statement without having a WHERE clause then all data fields from specified columns will be updated.

An UPDATE statement holds an exclusive lock on rows that are being modified and the lock got released once the transaction is completed. Lock level behaviors can change based on the isolation levels. The best practice to avoid higher-level locks is to execute UPDATE statements in batches and ensure any JOIN statement or condition-based filtering is supported by respective indexes.

There could also have a negative performance impact if you will not plan bigger updates carefully. This operation gets logged so do it during off business hours in batches to prevent its negative effect.

One thing you must consider while using aliases in the UPDATE statement is if you are using an alias for table expressions then make sure to use it everywhere in the SQL UPDATE statement because all references to the table expression must be matched in the UPDATE statement.

SQL UPDATE Syntax uses

This section will explain various use cases of SQL UPDATE statements. You will learn how to use SQL UPDATE statements:

• To modify single field
• To modify multiple fields
• To modify all column value
• To modify the current date-time value using the date-time function GETDATE
• To modify data field filtered by specific condition
• To modify data from another table

Let’s start with the first use case in which we will modify all data filed from a column, a single data field, and multiple data fields.

Use SQL UPDATE statement to modify single or multiple data fields

First, let me show you our source table in which I will perform all modifications. If you don’t have any table, you can create it and insert some values to learn and practice various uses of SQL UPDATE syntax.

An Employee table is created in a database TESTDB along with its data that is shown in the below image.

If you want to modify any column then you just need to use SQL UPDATE syntax with all required parameters to get this done. Suppose you want to update the employment nature of all employees to 1, you just need to execute a simple UPDATE statement for this column to modify it. Use below T-SQL statement as shown in the below query if you have a similar requirement.

The below output is showing that all 17 rows have been updated by executing the above statement.

Now, let’s verify the employment nature of each employee. We can see the difference; employment nature has been updated for all employees in the below image.

Now, let’s assume you need to modify a single data field of any of these employees like you have to modify the city or designation of any employee, we can do this using SQL UPDATE syntax.

Suppose Mary has been promoted to Sr Analyst, below query will help you to update Mary’s latest designation. I have executed the below statements to update Mary’s designation.

Always use unique ids to apply conditions to filter our desired targets for whom we need to perform updates. If you will use her name in the WHERE clause then this query will update the designation of all employees whose name is Mary that’s why I have used Mary’s ID or employee ID to update her specific details.

We can see the UPDATE statement has been executed successfully in the below image along with the second set of statements that is there to pull Mary’s details post modifying her designation. Now, her designation is showing as our desired value.

We can also modify multiple details of any specific employee in one shot. Let’s assume Suresh has been promoted from Sr Analyst to Specialist and his job location has also changed from Mumbai to New Delhi. You can run the below statement to make both changes using one single SQL UPDATE statement.

UPDATE Employee

See the output below, city and designation columns have been changed to our desired values. You can make changes in multiple columns by specifying columns and their respective value in the above statement.

SQL UPDATE statement using date-time functions

We can also use SQL UPDATE statements with date-time functions. Suppose you want to enter the current date-time value in a column then you can use the GETDATE function to fetch the current system time and update that in your desired column.

I have updated column Date in the below example where this column was not having any data before updating it using SQL UPDATE syntax whereas the second set of output is showing the current date-time in this column Date.

You can use TOP statement with UPDATE syntax as well the way I have used it in SELECT statement to return only the top 10 rows. Here, I have used another table Sales, and have updated the whole table that is why I have not used TOP statement in SQL UPDATE syntax.

Below is the output before updating the current date-time and after updating it in column Date. You can see data has been modified with the current timestamp. You can again apply filters using the WHERE clause if you have to modify specific data fields.

We can also use CAST and CONVERT functions along with the GETDATE() function in the above SQL UPDATE statement in case we need to store the current date-time in a different format.

SQL UPDATE syntax with subqueries and JOIN statement

Suppose you have added a column named POC (Point of Contact) in table Sales. The objective is to add a POC name for each sales company is doing for better service and customer experience. We already have the PersonID column in the Sales table, this column stores id of employees who have sold that product. Now, we need to add the name of that person id as POC in this newly added column. Here we need to fetch details from another table Employee and INSERT data in the Sales table.

Run the below statement to modify data from another table.

Below is the output where I have displayed before and after modification. You can compare POC names with their employee ID shown in the employee table.

We can also write the above query with a JOIN statement. Let me show you the below example to use SQL UPDATE syntax with JOIN statements.

Now, we have assigned POC for each of the sold products. Let’s say there is a requirement came to add a city column in the sales table and update this column for each sale based on the location of the POC or employee who sold the product. We will use a JOIN statement with SQL UPDATE syntax and let you demonstrate how to get this done.

I am displaying only the top 5 rows to keep my output result short, but the below query has updated this column for all the records stored in this column.

You can compare their cities from the first screenshot to validate whether modified data is correct or not.

Conclusion

The SQL UPDATE statement is used to modify data fields in SQL Server. This article has explained the basics of SQL UPDATE syntax and its use cases using which you can understand how to use SQL UPDATE statement to modify single or many data files in SQL Server. You also learn how to use it with expressions, subqueries, various SQL Server functions, and JOIN statements.

• Author
• Recent Posts

Manvendra Singh

Manvendra is a database enthusiast, currently working as a Senior Architect at one of the top MNC.

He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.

You can also find him on LinkedIn

View all posts by Manvendra Singh

Latest posts by Manvendra Singh (see all)

• Configure backup storage redundancy for Azure Cosmos DB account - May 15, 2024
• Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
• How to do a group by clause - April 12, 2023