SQL Server UPDATE

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the UPDATE statement to modify one or more existing records in a table.

What is UPDATE in SQL Server?

The UPDATE statement modifies existing records in a table or more precisely modifies one or more column values in one or more rows in a table. It is the most frequently used clause after SELECT since data update is a regular ongoing process. Since it has the capacity to modify existing live data it should be used very carefully with the right constraint (usually a WHERE clause). Otherwise, it can break the data by updating all values in the specified columns.

Operation

The UPDATE clause is used with and followed by the SET keyword and WHERE clause. The SET keyword defines the value to be updated in a column and the condition specified with WHERE clause tells SQL which rows to update in the table.

SQL Server UPDATE Syntax

The basic complete syntax of SQL Server UPDATE statement can be either of the below depending upon the source of the data.

In this case, the data is explicitly specified against the column.

UPDATE table
SET column_list = value_list
WHERE [condition];

In this case, the data is fetched from another table using a SELECT query.  Note the reference to 2 tables – table 1 and table 2.

UPDATE table1
SET column_list = (select column_list from table2 WHERE [condition])
WHERE [condition];

In this syntax,

  • UPDATE – clause used to modify column values in existing records in a table.
  • table1 – target table where the data is to be updated. (in case the source of data in another table)
  • SET – keyword used to specify comma separated column value combination.
  • column_list – the list of columns which should be updated with the new values.
  • value_list – the list of new values to be updated in the specified columns.
  • table2 – source table from which to fetch the data to be updated into the target table. (in case the source of data in another table)
  • WHERE conditions –   Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.

SQL Server UPDATE Examples

  • Columns are also referred to as fields or attributes and the terms are used interchangeably.
  • A row of information in a table is called a tuple.

Suppose we have a table called ‘employees’ containing the following information about the company’s employees. We will use it as the reference table to understand the different UPDATE usage scenarios.

emp_idemp_nameemp_hremp_typetech_tower
224Annie Bora                   Abid HussainContract           Wintel            
214Deep Sharma                  Abid HussainContract           Wintel
236Sunita Rai                   Sunidhi RaiPermanent          Unix               
221Rajesh Pandey                Abid HussainPermanent          Wintel
113Nitin Goyal                  Riya SebastianContract           Mainframe          
155Calvin Mathews               Zakiya KhanumPermanent          Linux              
111Jennifer Joseph              Zakiya KhanumPermanent          Linux              
243Syed Khan                    Riya SebastianPermanent          Mainframe          
109Sumit Singh                  Zakiya KhanumContract           Linux              
110Nupur Seth                   Riya SebastianPermanent          Mainframe          
Table Name:  employees

1) SQL Server UPDATE – single column example

We will start with the simplest example of updating a single column in the table. The following UPDATE query is an example which updates the tech_tower column value from ‘Wintel’ to ‘Windows’.

UPDATE employees
SET tech_tower='Windows'
WHERE tech_tower='Wintel';

After running the query, we can run a SELECT query which will show the change as below.

emp_idemp_nameemp_hremp_typetech_tower
224Annie Bora                   Abid HussainContract           Windows            
214Deep Sharma                  Abid HussainContract           Windows            
236Sunita Rai                   Sunidhi RaiPermanent          Unix               
221Rajesh Pandey                Abid HussainPermanent          Windows            
113Nitin Goyal                  Riya SebastianContract           Mainframe          
155Calvin Mathews               Zakiya KhanumPermanent          Linux              
111Jennifer Joseph              Zakiya KhanumPermanent          Linux              
243Syed Khan                    Riya SebastianPermanent          Mainframe          
109Sumit Singh                  Zakiya KhanumContract           Linux              
110Nupur Seth                   Riya SebastianPermanent          Mainframe          

2)  SQL Server UPDATE – multiple columns example

Now we will see an example of updating multiple columns. The below query updates the column values for 2 columns – emp_hr and tech_tower.

UPDATE employees
SET emp_hr='Yoganandh Lakshman'
,tech_tower='Wintel'
WHERE tech_tower='Windows';

After running the query, we can run a SELECT query which will show the change as below.

emp_idemp_nameemp_hremp_typetech_tower
224Annie Bora                   Yoganandh LakshmanContract           Wintel            
214Deep Sharma                  Yoganandh LakshmanContract           Wintel            
236Sunita Rai                   Sunidhi RaiPermanent          Unix               
221Rajesh Pandey                Yoganandh LakshmanPermanent          Wintel            
113Nitin Goyal                  Riya SebastianContract           Mainframe          
155Calvin Mathews               Zakiya KhanumPermanent          Linux              
111Jennifer Joseph              Zakiya KhanumPermanent          Linux              
243Syed Khan                    Riya SebastianPermanent          Mainframe          
109Sumit Singh                  Zakiya KhanumContract           Linux              
110Nupur Seth                   Riya SebastianPermanent          Mainframe          

3) SQL Server UPDATE – single column, different values example

In the above examples we saw how the UPDATE clause can be used to update one or more columns in a table with the new value specified. However, updating a column with a single value might not be enough always. Sometimes it is required to set different values for the different rows in a column. To understand the same first let us consider a table called towers containing information about the different technology projects.

tower_idtower_nametower_hrlive_projectsresource_count
200WintelYoganandh Lakshman59607
201UnixKavitha Shenoy18193
202LinuxZakiya Khanum44325
203MainframeRiya Sebastian20101
Table Name:  towers

Now suppose we want to update the resource count for some of the towers. We can do so by running the following query. It will update the resource count to the new value against the specified towers.

UPDATE towers
SET resource_count=CASE
WHEN tower_name='Wintel' THEN 577
WHEN tower_name='Unix' THEN 164
END
WHERE tower_name IN ('Wintel', 'Unix');

After running the query, we can run a SELECT query which will reflect the change.

tower_idtower_nametower_hrlive_projectsresource_count
200WintelYoganandh Lakshman59577
201UnixKavitha Shenoy18164
202LinuxZakiya Khanum44325
203MainframeRiya Sebastian20101

4)  SQL Server UPDATE – SELECT subquery example

In this example we will see how we can update a column in a record fetching the value from another table with a SELECT query. The following query is an example of the same where we update a value in the emp_hr column of the employees table from the tower_hr value in the towers table with the help of WHERE condition.

UPDATE employees
SET emp_hr=(SELECT tower_hr FROM towers WHERE tower_name='Unix')
WHERE tech_tower='Unix';

After running the query, we can run a SELECT query on the employees table which will reflect the change. We can see below that the emp_hr value has been changed from ‘Sunidhi Rao’ to ‘Kavitha Shenoy’ as per the tower_hr for Unix tower.

emp_idemp_nameemp_hremp_typetech_tower
224Annie Bora                   Yoganandh LakshmanContract           Wintel            
214Deep Sharma                  Yoganandh LakshmanContract           Wintel            
236Sunita Rai                   Kavitha ShenoyPermanent          Unix               
221Rajesh Pandey                Yoganandh LakshmanPermanent          Wintel            
113Nitin Goyal                  Riya SebastianContract           Mainframe          
155Calvin Mathews               Zakiya KhanumPermanent          Linux              
111Jennifer Joseph              Zakiya KhanumPermanent          Linux              
243Syed Khan                    Riya SebastianPermanent          Mainframe          
109Sumit Singh                  Zakiya KhanumContract           Linux              
110Nupur Seth                   Riya SebastianPermanent          Mainframe          

5)  SQL Server UPDATE – all rows example

In this example we will do an update without a WHERE condition constraint. As already cautioned before running an update query without any constraint will update all specified columns in all records of the table. Nevertheless, it might be required to do such an update sometimes. The following query is an example of the same where the emp_type is updated to ‘Permanent’ for all the employees.

UPDATE employees
SET emp_type=’Permanent’;

We can check the result by running a SELECT query on the employees table which will reflect the change.

emp_idemp_nameemp_hremp_typetech_tower
224Annie Bora                   Yoganandh LakshmanPermanent          Wintel            
214Deep Sharma                  Yoganandh LakshmanPermanent          Wintel            
236Sunita Rai                   Kavitha ShenoyPermanent          Unix               
221Rajesh Pandey                Yoganandh LakshmanPermanent          Wintel            
113Nitin Goyal                  Riya SebastianPermanent          Mainframe          
155Calvin Mathews               Zakiya KhanumPermanent          Linux              
111Jennifer Joseph              Zakiya KhanumPermanent          Linux              
243Syed Khan                    Riya SebastianPermanent          Mainframe          
109Sumit Singh                  Zakiya KhanumPermanent          Linux              
110Nupur Seth                   Riya SebastianPermanent          Mainframe          

Advertisement