In this article, we’ll walk-through the SQL update statement to modify one or more existing rows in the table.
In order to modify data in a table, we’ll use an Update statement, a DML (data manipulation language) statement. A SQL update statement comes with a SET clause where we define the column-and-value as a pair of items. In addition, you can enforce the conditional clause. In order to limit the number of rows, we’ll need to set up a where clause. The condition is defined in the where clause that identifies what rows to modify in the table.
After reading this article, you’ll understand the following topics covering how to use a simple SQL update statement
- on multiple columns
- with computed value
- with the compound operator
- with the defaults
- with SQL joins
- with the Where clause
- on a remote table
- with use Top(n) clause
- with CTE (Common-Table-Expression) statements
Running a simple SQL update statement
For this example, we’ll work with Person.Person , so, let’s take a look at the data first. In this case, let’s say, hypothetically, we wanted to change the data of the ModifiedDate column for all rows of the table with the current datetimestamp value.
Let us use the keyword UPDATE, and then the name of the table Person.Person, then use the keyword SET, and after that list the column name ModifiedDate and then the value, in this case, it’s current date timestamp.
|
1 2 3 4 |
USE AdventureWorks2014; GO UPDATE Person.Person SET ModifiedDate = GETDATE(); |
Using an update SQL statement with Multiple columns
Here, we’ve to come up with a pair of items, one being the column name, and one being the value, separated by an equal sign. The following example updates the columns Bonus with the value 8000, CommissionPct with the value .30, and SalesQuota by NULL for all rows in the Sales.SalesPerson table.
|
1 2 3 4 5 6 7 |
USE AdventureWorks2014; GO UPDATE Sales.SalesPerson SET Bonus = 8000, CommissionPct = .10, SalesQuota = NULL |
In this example, the above SQL update statement can be re-written using FROM clause and table alias.
The following example updates rows in the table Sales.SalesPerson. The table alias is created and assigned to “S” in the FROM clause. The alias is also specified as the target object in the UPDATE clause.
|
1 2 3 4 5 6 7 8 |
USE AdventureWorks2014; GO UPDATE S SET Bonus = 8000, CommissionPct = .30, SalesQuota = NULL FROM Sales.SalesPerson S; |
- Note: Executing a SQL Update statement without the where cases would actually update every record in the table.
Using an update SQL statement with a Where clause
In the following example, we only want to update one row of the Sales.SalesPerson table. In order to do that, we’ll need to use the WHERE clause. The where clause works exactly the same as they did with the SELECT statements. So, let’s add the keyword WHERE, and set a filter that specifies which record to modify. In this case, its BusinessEntityID is equal to 280. Now, we have an update SQL statement with SET, FROM and Where clause keywords.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT * FROM Sales.SalesPerson S WHERE BusinessEntityID = 280; USE AdventureWorks2014; GO UPDATE S SET Bonus = 8000, CommissionPct = .30, SalesQuota = NULL FROM Sales.SalesPerson S WHERE BusinessEntityID = 280; |
And now, we see that the columns Bonus, ComissionPct, and SalesQuota have been changed with new values.
|
1 2 3 |
SELECT * FROM Sales.SalesPerson S WHERE BusinessEntityID = 280; |
Using an SQL Update statement with a Top Clause
In the following examples, we can see that the use the TOP clause to limit the number of rows that are modified in the SQL UPDATE statement.
The following example updates the multiple columns of the matching rows in the Sales.SalesPerson table
|
1 2 3 4 5 6 7 8 |
USE AdventureWorks2014; GO UPDATE TOP (5) S SET Bonus = 90000, CommissionPct = .06, SalesQuota = NULL FROM Sales.SalesPerson S |
We can see that the SQL update ran over a random selection of rows.
|
1 2 3 |
SELECT * FROM Sales.SalesPerson S where CommissionPct = .06 |
- Note:
- When the TOP clause is used with any DML operation, the SQL update operation is performed on a random selection of ‘n’ number of rows
- You can also use the SET ROWCOUNT option, if you want to update the first set of rows without enforcing random selection using TOP keyword
Using an update SQL statement with a Top Clause using a CTE
As we all know that the SQL UPDATE statement with a TOP clause doesn’t support an ORDER BY clause but it is possible to get the sorted order of the columns using a CTE (Common Table Expression).
Let us run the same SQL update statement using a CTE
|
1 2 3 4 5 6 7 8 9 10 |
WITH CTE AS (SELECT TOP 5 * FROM Sales.SalesPerson ORDER BY BusinessEntityID) UPDATE CTE SET Bonus = 190000, CommissionPct = .07, SalesQuota = NULL; |
The output signifies the update ran over a order collection of BusinessEntityID.
|
1 2 |
SELECT * FROM Sales.SalesPerson where CommissionPct = .07 |
Using an SQL Update statement with Computed values
The following example uses computed value in SQL Update statement. The example increases the value of the Bonus column by 100 and ComissionPct column by 0.005 values for all rows of the BusinessEntityID equal to 288.
|
1 2 3 4 5 6 7 |
USE AdventureWorks2014; GO UPDATE Sales.SalesPerson SET Bonus = Bonus+100, CommissionPct = CommissionPct+0.005 WHERE BusinessEntityID = 288; |
Using an SQL Update statement with Compound operators
The following example uses the compound operator ‘+=’ and ‘*=’ to add 100 to the Bonus column and multiply 0.002 to CommissionPct column
|
1 2 3 4 5 6 7 8 9 10 11 |
USE AdventureWorks2014; GO SELECT * FROM Sales.SalesPerson WHERE BusinessEntityID = 289; GO UPDATE Sales.SalesPerson SET Bonus += 100, CommissionPct *= 0.005 WHERE BusinessEntityID = 289; GO SELECT * FROM Sales.SalesPerson WHERE BusinessEntityID = 289; |
Using an SQL Update statement with a default values
The following example sets the Primary column to its default value ((0)) for all rows that have a value equal to 1
|
1 2 3 4 5 |
USE AdventureWorks2014; GO UPDATE Production.ProductProductPhoto SET [Primary] = DEFAULT WHERE [Primary]=1 |
- Note: You can find the default definition of the column using the following T-SQL.
|
1 2 3 4 |
SELECT name,object_name(object_id),object_definition(default_object_id) Default_Definition FROM sys.columns WHERE name ='Primary' AND object_id = object_id('Production.ProductProductPhoto') |
Using an SQL Update statement with a SQL Joins
This example modifies the Rate column of the entire rows employee where they belong to the ‘Research and Development’ group.
|
1 2 3 4 5 6 7 8 |
UPDATE EPH SET EPH.Rate*=2 FROM HumanResources.EmployeePayHistory EPH INNER JOIN HumanResources.Employee EMP ON EMP.BusinessEntityID = EPH.BusinessEntityID INNER JOIN HumanResources.EmployeeDepartmentHistory H ON EMP.BusinessEntityID = H.BusinessEntityID INNER JOIN HumanResources.Department Dept ON H.DepartmentID = Dept.DepartmentID WHERE Dept.GroupName = 'Research and Development'; |
- Note: If you’re writing an UPDATE statement and you want to know how many rows might affect, You just need to place SELECT * FROM, keeping the same WHERE clause, and this will figure out how many rows that going to actually change.
|
1 2 3 4 5 6 7 |
SELECT EMP.JobTitle, EPH.Rate * 2 FROM HumanResources.EmployeePayHistory EPH INNER JOIN HumanResources.Employee EMP ON EMP.BusinessEntityID = EPH.BusinessEntityID INNER JOIN HumanResources.EmployeeDepartmentHistory H ON EMP.BusinessEntityID = H.BusinessEntityID INNER JOIN HumanResources.Department Dept ON H.DepartmentID = Dept.DepartmentID WHERE Dept.GroupName = 'Research and Development'; |
Remote tables using a linked server and OPENQUERY
The following example uses the linked server to update a data on a remote server.
|
1 2 3 4 5 6 |
UPDATE HQDBT01.AdventureWorks2014.HumanResources.Department SET GroupName = N'CIS Relations' WHERE DepartmentID = 4; SELECT GroupName FROM HQDBT01.AdventureWorks2014.HumanResources.Department WHERE DepartmentID = 4 |
In the following example, rows on the remote table is updated using the OPENQUERY rowset function
|
1 2 3 |
UPDATE OPENQUERY(HQDBT01, 'SELECT GroupName FROM AdventureWorks2014.HumanResources.Department WHERE DepartmentID = 4') SET GroupName = 'Sales and Marketing'; |
Summary
Thus far, we’ve discussed some of simple methods of updating rows using a SQL Update statement in SQL Server and various permutations using conditions, clauses and in other contexts. I hope you enjoyed reading this article and for any questions, please feel to comment below…
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021