SQL Server UPDATE Join

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use the UPDATE JOIN statement to modify existing records in a table from another table by creating a JOIN on the two tables.

What is UPDATE JOIN 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. The UPDATE JOIN is a special case of the UPDATE statement where records in one table (i.e. target table) are modified with corresponding values existing for the same column or field (maybe by another name but essentially the same) in another table (i.e. source table). Performing such an update requires us to create a JOIN on the two tables so that the matching data of the source table can be imported and updated into the target table.

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.  Additionally, in an UPDATE JOIN statement there is a JOIN clause to join the two tables together on a common field allowing the communication and movement of data between the tables.

SQL Server UPDATE Join Syntax

The basic syntax of SQL Server UPDATE JOIN statement is as below.

UPDATE table1
SET table1.column1 = table2.column1, …, table1.columnX=table2.columnX
FROM table1
INNER JOIN table2
ON table1.columnX=table2.columnX
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.
  • INNER JOIN – the list of columns which should be updated with the new values.
  • ON  – 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 JOIN 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.

To understand how the update join statement works first and foremost we will create 2 tables and insert some data into them. The first table is called customers and contains customer information and the second table is called addresses and contains address information.

CREATE TABLE customers (
cid INT IDENTITY(200,1) NOT NULL PRIMARY KEY
,fname VARCHAR(50)
,lname VARCHAR(50)
,czip VARCHAR(10)
,ccity VARCHAR(30)
,ccat VARCHAR(10));
CREATE TABLE addresses (
aid INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,cid INT
,azip VARCHAR(10)
,acity VARCHAR(30));

Now we will insert some data into the tables. In the customers table we will enter only the customer details and in the addresses table, we will enter the complete data. This is so that we can update the address related fields in the customers table from the addresses table using UPDATE JOIN.

INSERT INTO customers (fname, lname, ccat) VALUES ('James', 'Salvador', 'Premium'), ('Alice', 'Wells', 'Regular'), ('Herbert', 'Jones', 'Regular'), ('Evelyn', 'Smith', 'Regular'), ('Rick', 'Johnson', 'Premium');
INSERT INTO addresses (cid, azip, acity) VALUES (200, '61223', 'Detroit'), (201, '7145', 'Philadelphia'), (202, '68443', 'New York'), (203, '50675', 'Phoenix'), (201, '96573', 'Chicago');

The tables look like below after the insert.

cidfnamelnameczipccityccat
200JamesSalvadorNULLNULLPremium
201AliceWellsNULLNULLRegular
202HerbertJonesNULLNULLRegular
203EvelynSmithNULLNULLRegular
204RickJohnsonNULLNULLPremium
aidcidazipacity
120061223Detroit
22017145Philadelphia
320268443New York
420350675Phoenix
520496573Chicago

We will now execute the following UPDATE JOIN query to populate address related fields in the target customers table from the source addresses table.  Please note that cust and addr have been used as aliases for customers and addresses tables respectively.

UPDATE cust
SET cust.czip = addr.azip
,cust.ccity = addr.acity
FROM customers cust
INNER JOIN addresses addr
ON cust.cid = addr.cid;

After the update if we run a SELECT query on the customers table, we will find that the address related fields czip and ccity have been populated with data from the source address table.

cidfnamelnameczipccityccat
200JamesSalvador61223DetroitPremium
201AliceWells7145PhiladelphiaRegular
202HerbertJones68443New YorkRegular
203EvelynSmith50675PhoenixRegular
204RickJohnson96573ChicagoPremium

Advertisement