In this article, we will learn, How can I do an UPDATE statement with JOIN in SQL Server? I’m working on project a in which I came across a situation where I have to write SQL update query using joins. Now we will see some of the easiest techniques here.

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 : Update a table using JOIN in SQL Server

As you can see in the below image , I have created two table 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‘ filed in Department.For that we can use the below 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;

The post [Solved]-How To Update a Table using JOIN in SQL Server? appeared first on Software Development | Programming Tutorials.



Read More Articles