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] [int] NOT 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 = ON) ON [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] [int] NOT NULL,
  3. [Bank_id] [int] NOT 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 = ON) ON [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.


  2. UPDATE T
  3. SET T.SwitCode=SC.Code
  4. FROM Bank T
  5. 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