Update statement

In the previous chapter, we discussed the T-SQL’s Insert statement. Now, in this chapter, we are going to discuss the T-SQL’s UPDATE command. We will discuss how we can use the T-SQL’s Update command with the help of examples. We will also discuss some best practices we need to follow while using the UPDATE command in SQL Server.

What is UPDATE command

UPDATE command is used to modify existing records in a SQL table. Depending on the need, we can update all or a few selected records in a table. Also, we can update the records in a table based on the records of other tables. However, in this case, we need to join all the tables in the FROM clause of the UPDATE statement. Let’s have a look at the syntax of the UPDATE statement which updates all rows of the table:

Syntax of UPDATE command in SQL Server

To update all the records in a table, below is the syntax:

UPDATE [<schema-name>.]<table-name>

SET <column-1> = <value-1>,

[<column-2> = <value-2>,]

[<column-3> = <value-3>,]

[<column-4> = <value-4>,]

[………………………………….]

[<column-n> = <value-n>,]

Where,

UPDATE is used to start a T-SQL’s UPDATE command.

[<schema-name>.] is the name of the schema in which the table exists and is optional if the object exists in the default schema that is “dbo”.

<table-name> is the table in which records need to be updated.

SET is used to define the list of the columns and their values. Remember that all these columns must be a part of that table which we are going to update and declared just after the UPDATE statement.

<column-1> = <value-1> is the name of the first column and the corresponding value which needs to be updated in the given table. We provide all the column names along with their corresponding values separated with an equals (=) sign. Remember that we can update one or multiple columns of a table in a single UPDATE statement. In the above syntax [<column-2> = <value-2>,] to [<column-n> = <value-n>,] part is optional and is used if we need to update multiple columns.

Be cautious while executing the UPDATE commands. Make sure that the required WHERE clause and JOIN conditions are in place before executing the UPDATE command on a table. As a best practice, we can use transactions which allows us to rollback or commit the changes accordingly.

Example – UPDATE all records of a table

Suppose, we want to update the Adress of all the employees in the dbo.Employee table which we have created in the previous chapter. We need to update the Adress column values from “Delhi” to “New Delhi“. In order to do that, we can use below UPDATE statement:

UPDATE dbo.Employee
SET [Address] = 'New Delhi'

Please mind the single quotation marks (‘) inside which the text ‘New Delhi’ is wrapped. In SQL Server, we need to pass string values in this way. The double quotation marks do not work as a replacement hence can not be used to wrap string values.

UPDATE selected records

Below is the syntax to update only those records which are satisfying a given condition:

UPDATE [<schema-name>.]<table-name>

SET <column-1> = <value-1>,

[<column-2> = <value-2>,]

[<column-3> = <value-3>,]

[<column-4> = <value-4>,]

[………………………………….]

[<column-n> = <value-n>,]

WHERE <filter-condition>

Where,

WHERE is the WHERE clause which is used to specify a boolean condition.

<filter-condition> is the filter which needs to be applied on the table before UPDATE command gets executed.

Example – UPDATE selected records

Suppose, in the dbo.Employee table, we want to update the Address of Employee 1 (whose Employee Id is 100) to “New Delhi, India” from “New Delhi“. In order to do that we need to use below UPDATE statement:

UPDATE dbo.Employee
SET [Address] = 'New Delhi, India'
WHERE EmpId = 100

UPDATE records based on multiple tables

Below is the syntax to UPDATE records in a table based on other tables:

UPDATE [<schema-name>.]<table-1>

SET <column-1> = <value-1>,

[<column-2> = <value-2>,]

[<column-3> = <value-3>,]

[<column-4> = <value-4>,]

[………………………………….]

[<column-n> = <value-n>,]

FROM <table-1>

JOIN <table-2> ON <table-1>.<column-1> = <table-2>.<column-2>

WHERE [<filter-condition>]

Where,

FROM clause is the start of a FROM clause which is used to define the source table name and other JOIN conditions.

<table-1> is the name of the table we are going to update.

JOIN <table-2> ON <table-1>.<column-1> = <table-2>.<column-2> is the JOIN condition which JOINS other tables to the table which needs to be updated. We can have multiple JOIN conditions as we do in SELECT statements.

It is recommended to convert the UPDATE query into a SELECT query and verify the records first. Then, if it looks fine, we can execute the original UPDATE statement.

Example – UPDATE records based on other tables

Let’s create a dbo.Department and dbo.EmployeeDepartmentMapping tables with some dummy data so that we can JOIN these tables with the existing Employee table and modify the records accordingly. The create table and insert statement for the dbo.Employee table can be taken from the previous chapter. However, the below script includes the CREATE TABLE and INSERT commands for the dbo.Employee table along with dbo.Department and dbo.EmployeeDepartmentMapping tables. This script will drop and recreate all these tables in the database.

--Create Employee table with sample data
IF OBJECT_ID('dbo.Employee') IS NOT NULL
	DROP TABLE dbo.Employee;
GO
CREATE TABLE dbo.Employee
(
	EmpId INT NOT NULL,
	EmpName VARCHAR(256) NOT NULL,
	Address VARCHAR(512)
);
GO

INSERT INTO dbo.Employee
(EmpId, EmpName, Address)
VALUES (100, 'Employee 1', 'Delhi');
GO

--Insert more employees dynamically into Employee table
INSERT INTO dbo.Employee (EmpId, EmpName, Address)
VALUES
(200, 'Employee 2', 'Delhi'),
(300, 'Employee 3', 'Delhi');
GO

INSERT INTO dbo.Employee (EmpId, EmpName, Address)
SELECT TOP 10
(100 * (3 + ROW_NUMBER() OVER(ORDER BY (SELECT 0)))) AS EmpId,
'Employee ' + CAST((3 + ROW_NUMBER() OVER(ORDER BY (SELECT 0))) AS VARCHAR(10)) AS EmpName,
'Delhi' AS Address
FROM sys.objects;
GO


--Create Department and EmployeeDepartmentMapping tables with sample data
IF OBJECT_ID('dbo.Department') IS NOT NULL
	DROP TABLE dbo.Department;
GO
CREATE TABLE dbo.Department
(
	DeptId INT NOT NULL,
	DeptName VARCHAR(256)
);
GO

INSERT INTO dbo.Department
(DeptId, DeptName)
VALUES
(1, 'IT'),
(2, 'HR');
GO

IF OBJECT_ID('dbo.EmployeeDepartmentMapping') IS NOT NULL
	DROP TABLE dbo.EmployeeDepartmentMapping;
GO
CREATE TABLE dbo.EmployeeDepartmentMapping
(
	DeptId INT,
	EmpId INT,
	IsActive BIT
);
GO

INSERT INTO dbo.EmployeeDepartmentMapping
(DeptId, EmpId, IsActive)
VALUES
(1, 100, 1),
(1, 200, 1),
(2, 300, 1),
(2, 400, 1);
GO

Below is the screenshot of all three tables.

Now, suppose we want to update the Address of all the employees working for Department 1 (which has DeptId 1) where employee and department mapping is currently active (IsActive = 1). We need to update the Address to ‘New Delhi, India’. Below is the UPDATE query which joins the Employee table with Department and EmployeeDepartmentMapping tables, and updates the Employee table accordingly.

UPDATE dbo.Employee
SET [Address] = 'New Delhi, India'
FROM dbo.Employee e
INNER JOIN dbo.EmployeeDepartmentMapping ed ON e.EmpId = ed.EmpId
INNER JOIN dbo.Department d ON ed.DeptId = d.DeptId
WHERE d.DeptId = 1 AND ed.IsActive = 1;
GO

Thanks for the reading and please rate this post. If you like this post, please do share this on your social network.