[SOLVED]-HOW TO UPDATE A TABLE USING JOIN IN SQL SERVER ?
HOW TO UPDATE A TABLE USING JOIN IN SQL SERVER ?
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.
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=9Cretae 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;