[Solved]-How To Update a Table using JOIN in SQL Server?

In this article, we will learn how to perform an UPDATE statement with JOIN in SQL Server. While working on a project, I encountered a situation where I needed to write an SQL update query using joins. Now, we will explore some of the easiest techniques to accomplish this task.

Generic Syntext Query:

UPDATE tb1
SET foo = tb2.col
FROM Table1 tb1
JOIN Table2 tb2
    ON tb1.col1 = tb2.colx
WHERE .your condition for updating value.

Sql Query Example : 

As you can see in the image below, I have created two tables: Company_Employees and Department. Company_Employees has a foreign key relationship with the Department table.

Now, I want to update the 'Designation' column with the 'DepartmentName' field from the Department table. For that, we can use the following query:

UPDATE tb1
SET Designation = tb2.DepartmentName
FROM Company_Employees tb1
JOIN Department tb2
    ON tb1.DepId = tb2.Id
WHERE tb1.Id=9

Cretae Script of Both Table for Practice

CREATE TABLE [dbo].[Company_Employees](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [First_Name] [nvarchar](200) NULL,
    [Middle_Name] [nvarchar](200) NULL,
    [Last_Name] [nvarchar](200) NULL,
    [Address] [nvarchar](500) NULL,
    [Designation] [nvarchar](200) NULL,
    [DepId] [int] NULL,
 CONSTRAINT [PK_TblUsers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Department](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert Query :

INSERT [dbo].[Company_Employees] ([Id], [First_Name], [Middle_Name], [Last_Name], [Address], [Designation], [DepId]) VALUES (9, 'John', 'eric', 'smith', 'Paris', 'Data Analyst', 1)
GO
INSERT [dbo].[Company_Employees] ([Id], [First_Name], [Middle_Name], [Last_Name], [Address], [Designation], [DepId]) VALUES (1010, 'Ram', 'Kumar', 'Verma', 'New Delhi', NULL, 2)
GO

GO
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (1, 'Data Analyst')
GO
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (2, ' Associate Data Engineer
')
GO
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (3, 'Business Intelligence Manager')
GO
INSERT [dbo].[Department] ([Id], [DepartmentName]) VALUES (4, 'Data Engineering')

 
Generic Query:

Update t1.Column1 = value 
from table_name1 as t1 
inner join table_name2 as t2 on t2.ID = t1.SomeId 
where t1.[column1]=value and t2.[Column1] = value;

This SQL query is an UPDATE statement that modifies data in one table (table_name1) based on a condition involving another table (table_name2). 

• Update t1.Column1 = value: It sets the value of Column1 in table_name1 to the specified value.
• FROM table_name1 as t1 INNER JOIN table_name2 as t2 ON t2.ID = t1.SomeId: This part of the query performs an inner join between table_name1 (aliased as t1) and table_name2 (aliased as t2) based on the condition t2.ID = t1.SomeId. This means that only rows with matching values of ID in table_name2 and SomeId in table_name1 will be included in the result set.
• WHERE t1.[column1]=value and t2.[Column1] = value: This part of the query adds additional conditions to filter the rows. It ensures that only rows where column1 in table_name1 and Column1 in table_name2 have the specified value are considered for the update operation.

This query updates Column1 in table_name1 for rows where the value of column1 in table_name1 and the value of Column1 in table_name2 are both equal to the specified value, and the ID in table_name2 matches the SomeId in table_name1. It's essentially updating records in table_name1 based on a condition involving another related table table_name2.d