update delete statement with inner join in sql server

Introduction:

In this article I will explain how to use delete or update statement with inner join in SQL Server.

Description:

In previous post I explained how to use update statement with replace function in SQL Server and many articles relating to SQL Server. Now I will explain how to use delete or update statement with inner join in SQL Server.  In one application I got requirement like delete data from database based on multiple table column values in SQL Server. Generally we will use joins concept to get data from multiple tables and if we want to delete data based on the column values of multiple tables we will use subqueries instead of use multiple subqueries we can reduce it by using inner join with delete statement.

Now I will explain how to use delete or update statement with inner join in SQL server. For that first design two tables (EmployeeDetails, SalaryDetails) in your database as shown below

EmployeeDetails Table

Column Name	Data Type	Allow Nulls
EmpId	Int (set Identity=true)	No
EmpName	varchar(50)	Yes
Role	Varchar(50)	Yes

SalaryDetails Table

Column Name	Data Type	Allow Nulls
SalId	Int (set Identity=true)	No
Salary	Int	Yes
EmpId	int	No

Once tables design done then please enter some data in tables as show below

SalaryDetails                                     EmployeeDetails

Now I will show you how to write update query with inner join as shown below

UPDATE E set E.EmpName='SureshDasari' FROM EmployeeDetails E INNER JOIN SalaryDetails S ON E.EmpId=S.EmpId WHERE EmpName='Suresh'

Syntax for Delete Query with inner join will be like this

DELETE FROM table1 FROM table1 INNER JOIN table2 ON table1.columnname= table2.columnname

Example will be like this

DELETE FROM EmployeeDetails FROM EmployeeDetails E INNER JOIN SalaryDetails S ON E.EmpId=S.EmpId WHERE EmpName='SureshDasari'

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email