Updating A Table Using Join In SQL Server

Today, we will learn how to update a table using Join in SQL Server. We have two tables - Bank and SwitCode.

Bank Table
Given below is the script for creating the Bank table.

  1. CREATE TABLE [dbo].[Bank](  
  2.     [id] [intNOT NULL,  
  3.     [BankName] [nvarchar](50) NULL,  
  4.     [SwitCode] [nvarchar](50) NULL,  
  5.  CONSTRAINT [PK_Bank] PRIMARY KEY CLUSTERED   
  6. (  
  7.     [id] ASC  
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  9. ON [PRIMARY]  
Join In SQL Server
 Fig: Bank table with data 

SwitCode Table
Following is the script for creating the SwitCode table.

  1. CREATE TABLE [dbo].[SwitCode](  
  2.     [id] [intNOT NULL,  
  3.     [Bank_id] [intNOT NULL,  
  4.     [Code] [nvarchar](50) NULL,  
  5.  CONSTRAINT [PK_SwiftCode] PRIMARY KEY CLUSTERED   
  6. (  
  7.     [id] ASC  
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  9. ON [PRIMARY]  
 Join In SQL Server

Fig: SwitCode table with data.

Condition
SwitCode has to update from SwitCode table using Bank_id.

Below is the query.


  1. UPDATE T  
  2. SET T.SwitCode=SC.Code  
  3. FROM Bank T  
  4. INNER JOIN SwitCode SC ON SC.Bank_id=T.id  

 After executing the above query, we will get the desired output.
 
Join In SQL Server
X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now