The SQL Server UPDATE statement is one of the most commonly used DML (Data Manipulation Language) statements for modifying the existing data in a database table. Unlike the DELETE command, the UPDATE command updates or replaces the table records based on the condition. While updating a row, it will lock that row before the modification and release the lock once the update is completed.
SQL Server UPDATE statement allows you to update a single or multiple columns with the help of a condition specified in the WHERE clause. However, you can omit the condition to update the whole table.
The following are some of the situations where we can use the SQL Server UPDATE statement, but are not limited to.
- When there are data entry mistakes while inserting records.
- The present table data is irrelevant, and you want to update it with another latest source table.
- Updating the inactive records in the table.
- When you want to update a single row or multiple rows or columns based on an expression. For instance, updating the black color product price, salary hikes, investment updates for the latest technologies, or trending products.
SQL UPDATE Statement Syntax
The syntax of the UPDATE Statement is as shown below.
UPDATE [Table_Name]
SET [Column1] = [Value1],
[Column2] = [Value2],
[ColumnN] = [ValueN]
WHERE Condition
- Here, the UPDATE keyword informs the SQL server that you want to modify/update a certain record.
- Column1…ColumnN: Column names on which you want to change.
- Value1…ValueN: Provide the new Values of the columns.
- The SET keyword will assign New values to the columns.
- WHERE Condition: Here, we have to provide filters or conditions. If the condition is TRUE, then only the query will update the records.
NOTE: If you miss the Where Clause, it updates all the rows that belong to a given column in a table.
The following series of examples helps you understand the SQL Server UPDATE statement on single/multiple columns and with/without a WHERE clause. Apart from that, how to use UPDATE in combination with LIKE, JOINS, the OUTPUT clause, etc. To demonstrate all these, we have created two simple tables using the following query. We will mostly use the employee table to perform the different types of update operations.
CREATE TABLE department (
Dept_id INT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL
);
CREATE TABLE employee (
id INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Education VARCHAR(100),
HireDate DATE,
Salary MONEY,
Sales MONEY,
Dept_id INT,
FOREIGN KEY (Dept_id) REFERENCES department(Dept_id)
);
INSERT INTO department VALUES (1, 'Sales');
INSERT INTO department VALUES (2, 'Manager');
INSERT INTO department VALUES (3, 'HR');
INSERT INTO employee VALUES ('John', 'Masters Degree', '2009-01-14', 2000000, 5000, 1);
INSERT INTO employee VALUES ('Bruce', 'Degree','2008-01-31', 800000, 7000, 1);
INSERT INTO employee VALUES ('Teddy', 'B.Tech', '2007-12-05',1500000, 10000, 2);
INSERT INTO employee VALUES ('Julie', 'B.Tech', '2010-12-06', 1200000, 0, 3);
INSERT INTO employee VALUES ('Tracy', 'Partial High School', '2012-05-30', 500000, 2000, 1);
SQL UPDATE Statement on a Single Column
While working with the UPDATE Statement, the WHERE clause plays a vital role. In the coming sections, we will discuss the consequences of its absence, but for now, we will use it.
One of the most common scenarios is updating a single record based on a particular condition. For instance, updating the age of an employee or customer address, or email ID, etc. The query below will update the Education from Partial High School to Degree for the employee named Tracy.
UPDATE employee
SET Education = 'Degree'
WHERE Name = 'Tracy'
Please refer to the table below to see the changes.
UPDATE Multiple Records
Apart from that, you can use the SQL UPDATE statement to change multiple rows in a single column. For instance, we want to increase the salary of the employees by 25000 who have a Bachelor of Technology certificate. In such a case, the following query updates the salary of all the employees with a ‘B.Tech’ degree.
UPDATE employee
SET Salary = Salary + 25000
WHERE Education = 'B.Tech'
After every UPDATE statement, use the query below to see the data inside the employee table after the changes have happened.
SELECT * FROM employee
The result is:
id Name Education HireDate Salary Sales Dept_id
1 John Masters Degree 2009-01-14 2000000.00 5000.00 1
2 Bruce Degree 2008-01-31 800000.00 7000.00 1
3 Teddy B.Tech 2007-12-05 1525000.00 10000.00 2
4 Julie B.Tech 2010-12-06 1225000.00 0.00 3
5 Tracy Degree 2012-05-30 500000.00 2000.00 1TIP: Please understand the Table design before updating any records; otherwise, you will end up with errors. For instance, updating identity columns, primary key columns, adding NULLs to NOT NULL columns, etc.
SQL UPDATE Statement on Multiple Columns
In the above example, we used the WHERE clause to update records or rows in a single column. However, you can use the combination of the UPDATE statement and WHERE clause to modify multiple columns with a single statement. For instance, changing the Department name and the Manager name at the same time for the newly shifted employees.
NOTE: While updating multiple columns in a single SQL Server UPDATE statement, we must use the comma to separate those columns.
In this example, we will update the employee’s salary by adding 15000 to the existing amount and set the sales value as 100 for employee ID four.
UPDATE employee
SET Salary = Salary + 15000, Sales = 100
WHERE id = 4
--WHERE Name = 'Julie'
As you can see from the table below, ID number 4 has updated the salary and sales.
SQL UPDATE Multiple Rows and Columns
Imagine that you want to update the sales and the salary of the employee belonging to the Degree Education. It means we want to update multiple columns and rows at the same time using a single UPDATE statement.
The query below updates salary by adding 77000 to it and 250 to sales for the employees who have a Degree certificate.
UPDATE employee
SET Salary = Salary + 77000, Sales = Sales + 250
WHERE Education = 'Degree'
SQL UPDATE Statement With Expressions
It is another example where we want to update the salary of the B.Tech employees by 20%.
UPDATE employee
SET Salary = Salary * 1.20
WHERE Education = 'B.Tech'
The Result is:
id Name Education HireDate Salary Sales Dept_id
----------- --------------------------------------------------------------------------------------------
1 John Masters Degree 2009-01-14 2000000.00 5000.00 1
2 Bruce Degree 2008-01-31 877000.00 7250.00 1
3 Teddy B.Tech 2007-12-05 1830000.00 10000.00 2
4 Julie B.Tech 2010-12-06 1488000.00 100.00 3
5 Tracy Degree 2012-05-30 577000.00 2250.00 1
DEFAULT Values
If your table has the DEFAULT values and you want to update the column with the default information, you can use the SQL UPDATE statement. For instance, there is a default Education information as ‘High School ‘, and you want to update the particular employee.
UPDATE employee
SET Education = DEFAULT
WHERE Name = 'Bruce'
UPDATE With TOP clause
We can use the combination of the SQL UPDATE statement and the TOP clause to modify the top n number or percentage of rows. For instance, if you want to update the employee salary by an increment of 50% for the top 20 employees based on their commitment towards the organization.
UPDATE TOP (20) employee
SET Salary = Salary * 1.50
SQL UPDATE Statement using DATETIME functions
Apart from the regular columns, you can use the UPDATE statement to update the DATE and TIME columns or use them inside the WHERE condition to update regular columns. For instance, if you want to update the order or shipping date to the current date for a particular product, you can write the following query.
UPDATE products
SET ShippingDate = GETDATE()
WHERE productName = 'Mobile'
-- WHERE productid = 2
Similarly, you can use the DATE and TIME functions in the WHERE condition to update the regular columns. The query below will increase the employees’ salary by 50% who have been working for more than 15 years.
UPDATE employee
SET Salary = Salary + Salary * 0.50
WHERE DATEDIFF(year, HireDate, GETDATE()) > 15
Output
id Name Education HireDate Salary Sales Dept_id
1 John Masters Degree 2009-01-14 3000000.00 5000.00 1
2 Bruce Degree 2008-01-31 1315500.00 7250.00 1
3 Teddy B.Tech 2007-12-05 2745000.00 10000.00 2
4 Julie B.Tech 2010-12-06 1488000.00 100.00 3
5 Tracy Degree 2012-05-30 577000.00 2250.00 1SQL UPDATE Statement without WHERE Clause
In our previous examples, we have shown the importance of using the WHERE clause in the UPDATE statement. If you accidentally or intentionally want to remove the WHERE condition, the UPDATE statement will modify all the rows present in the given column. It is like a complete table update that may not be the case in most scenarios.
Although it has its own disadvantages, first, let me show you the main purpose of using the SQL UPDATE statement without a WHERE condition. Suppose the government has increased some Tax values to 0.5% for all Goods and Services (or any particular Good). Then we can use this statement to change those values with a new one. OR you want to increase the prices of your goods, then you can use it.
Assume that a company has made into profit and wants to increase the salary of all the employees in the organization by 10%. In such a case, you write the following query to update the salary by a 10% hike.
UPDATE employee SET Salary = Salary + Salary * 0.10
The above-mentioned example was one of its kind, but in most cases, omitting the WHERE clause in the UPDATE statement is a disaster. For instance, the query below will update the salary of all employees to 900000, which is a mistake. So, please be careful with conditions.
UPDATE employee SET Salary = 900000
Here, we haven’t specified the WHERE condition. That’s why the Server changed all six records.
SQL UPDATE Statement with Subquery
While performing the normal modifications, the user can provide the required values. However, there are situations where we have to use the subquery with the UPDATE statement to modify the existing values based on the result returned by the subquery.
Imagine that you want to increase the employee salaries who made the highest sales, i.e., greater than the average. In such a case, use the subquery to find the average employee sales. Next, the WHERE clause in the main UPDATE statement query modifies the salary of those by increasing the amount by a 25% bonus.
UPDATE employee SET Salary = Salary + Salary * 0.25 WHERE Sales > (SELECT AVG(Sales) FROM employee)
The result table is:
id Name Education HireDate Salary Sales Dept_id
1 John Masters Degree 2009-01-14 3000000.00 5000.00 1
2 Bruce Degree 2008-01-31 1315500.00 7250.00 1
3 Teddy B.Tech 2007-12-05 2745000.00 10000.00 2
4 Julie B.Tech 2010-12-06 1488000.00 100.00 3
5 Tracy Degree 2012-05-30 577000.00 2250.00 1How to UPDATE a Table Data from Another Table?
If you look at the output of the above query, the salary of all the employees has changed to 90000, and there are a couple of changes in the Education and Sales as well. Here, we want to update the salary column using the backup table.
The basic syntax of using the SQL Server UPDATE Statement to modify records of a first table using data from a second table is as shown below.
UPDATE [Table1] SET [Column1] = (SELECT expression/Column FROM Table2 WHERE Condition) WHERE Condition
In this example, we show how to update a table’s data using records from another table. Here, we refreshed the record in the employee table with the data from another emp Table.
UPDATE employee SET Salary = (SELECT Salary FROM emp WHERE employee.id = emp.id)
If there is a situation where the table has a new column and it has to update information from another table. In such a case, you can use the above technique or JOINS. For instance, let me alter the table and add a new column to enter the department name.
ALTER TABLE employee ADD Department VARCHAR(100) NULL
The SQL Server query below uses the Dept_id as the common column between the two tables and updates the Department column with the values from another table. Please be careful with the WHERE condition, if you replace the Dept_Id with id (i.e., employee id) the result will be a great mistake.
UPDATE employee SET Department = ( SELECT DepartmentName FROM department WHERE employee.[Dept_id] = department.[Dept_id] )
SQL UPDATE Statement with JOIN
We will take the same example as we mentioned previously to demonstrate the JOIN function with the UPDATE statement. Here, we will update the newly created Department column using the department table. It is the best approach than the previous one.
UPDATE employee SET employee.Department = d.DepartmentName FROM employee e JOIN department d ON e.Dept_id = d.Dept_id
The result is:
id Name Education HireDate Salary Sales Dept_id
1 John Masters Degree 2009-01-14 1125000.00 5000.00 1
2 Bruce Degree 2008-01-31 1125000.00 7250.00 1
3 Teddy B.Tech 2007-12-05 1125000.00 10000.00 2
4 Julie B.Tech 2010-12-06 900000.00 100.00 3
5 Tracy Degree 2012-05-30 900000.00 2250.00 1To demonstrate the same, before using the above query, use the below one to update the Department with NULL values. Next, write the above query to test it.
UPDATE employee
SET Department = NULL
Once the execution completes, use the query below to delete the newly created column.
ALTER TABLE employee
DROP COLUMN Department
In the previous section, we used the SQL query to update the employee’s Salary. However, we must update the Education and Sales as well to get the table to its original position. In such a case, you can use the backup table as the JOIN reference and update the employee table with old values.
UPDATE employee
SET employee.Education = emp.Education,
employee.Salary = emp.Salary,
employee.Sales = emp.Sales
FROM employee e JOIN emp ON e.id = emp.id
Using UPDATE with the LIKE operator
The SQL Server UPDATE statement allows you to use the LIKE operator to perform the modification based on a wildcard search. For instance, the following LIKE query updates the salary and sales for all the employees whose name starts with T.
UPDATE employee
SET Sales = 20000,
Salary = Salary + 29000
WHERE Name LIKE 'T%'
The result set:
id Name Education HireDate Salary Sales Dept_id
1 John Masters Degree 2009-01-14 2000000.00 5000.00 1
2 Bruce Degree 2008-01-31 800000.00 7000.00 1
3 Teddy B.Tech 2007-12-05 1529000.00 20000.00 2
4 Julie B.Tech 2010-12-06 1200000.00 0.00 3
5 Tracy Partial High School 2012-05-30 529000.00 20000.00 1SQL UPDATE Statement Stored Procedure
In this example, we use the UPDATE statement inside a stored procedure. Although there are multiple examples with input parameters, we will use a simple example. Here, the UPDATE statement inside the stored procedure checks if the sales of each employee are less than the average. If true, it will change the salary of employees by a 5% decrease.
IF OBJECT_ID ( 'sp_Example', 'P' ) IS NOT NULL
DROP PROCEDURE sp_Example;
GO
CREATE PROCEDURE sp_Example
AS
BEGIN
SET NOCOUNT ON;
UPDATE employee
SET Salary = Salary - Salary * 0.05
WHERE Sales < (SELECT AVG(Sales) FROM employee)
END
GO
If you run the above query, it will show the below message.
Messages
-------
Command(s) completed successfully.Let me use the EXEC Command (Execute Command) to execute the UPDATE stored procedure
EXEC dbo.sp_Example
Now, let us see whether the execution of the stored procedure altered the Last Name and Occupation or not
SELECT * FROM employee
SQL UPDATE Statement with OUTPUT Clause
Microsoft provides a beautiful concept of the OUTPUT clause. When you use the OUTPUT clause with the UPDATE statement, the query will not only change the records but also return the information of the changes and the affected row. It provides valuable information for the query executor to understand exactly what happened.
The query below will increase the Sales value by adding 5000 to each employee who has a Degree or a Master’s Degree.
UPDATE employee
SET Sales = Sales + 5000
OUTPUT INSERTED.id, INSERTED.Name, INSERTED.Education, INSERTED.Sales
WHERE Education LIKE '%D%'
The OUTPUT clause in the above SQL UPDATE statement query will return the employee id, Name, education, and Sales information as the result.
id Name Education Sales
1 John Masters Degree 10000.00
2 Bruce Degree 12000.00The final result after the OUPUT statement is:
id Name Education HireDate Salary Sales Dept_id
1 John Masters Degree 2009-01-14 1900000.00 10000.00 1
2 Bruce Degree 2008-01-31 760000.00 12000.00 1
3 Teddy B.Tech 2007-12-05 1529000.00 20000.00 2
4 Julie B.Tech 2010-12-06 1140000.00 0.00 3
5 Tracy Partial High School 2012-05-30 529000.00 20000.00 1SQL Server UPDATE Statement using Management Studio
If you can access the Management Studio, use Intellisense to generate the UPDATE Statement. To do so, right-click on the Table, and Select Script Table as -> UPDATE TO -> New Query Editor Window.
Once you select the New Query Editor Window option, it will generate the following statement query. As you can see, it displays all the column names and the data type it will accept. All you have to do is replace the text inside the <> with your required value and modify the where condition.
The Code generated by the Management Studio is:
UPDATE [dbo].[employee]
SET [Name] = <Name, varchar(100),>
,[Education] = <Education, varchar(100),>
,[HireDate] = <HireDate, date,>
,[Salary] = <Salary, money,>
,[Sales] = <Sales, money,>
,[Dept_id] = <Dept_id, int,>
WHERE <Search Conditions,,>
You can use any of the above-mentioned examples to check this code.
Option 2: Edit Top 200 Rows
There is an option called the Edit Top 200 Rows. Right-click on the table and choose the Edit Top 200 Rows option. You can update the values as per your need, but limited to 200.
SQL UPDATE Statement Best Practices
- Always back up the tables (data) before performing the UPDATE statement.
- Use the WHERE clause to avoid updating all the records in a table. Utilize the indexed column in the WHERE clause for query performance.
- Before updating any record, use the SELECT statement to check whether we want to modify it or not.
- Insert the complete UPDATE statement inside the BEGIN and END TRANSACTIONS to commit or rollback the complete process based on the requirement.
- Use a TRY CATCH block to deal with errors.