Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

update delete statement with inner join in sql server

Jun 19, 2012
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 RSS subscribe by email Subscribe by Email

7 comments :

sneh said...

hie suresh...

Nice to see your article but my question is that.
Is it mandatory to have common fields for join???

Anonymous said...

Name of field can be any but datatype should be same.

Anonymous said...

idiota

Unknown said...

i cant do it .. its showing error while deleting it ...
while deleting images have i to first unlink it and then deleting from two tables having common feilds ....
plzz help me ...
i cant do it ...
thnx in advance .. :)

Unknown said...

you have mentioned incorrect table names. you mixed them. salary table you mentioned as employee table and vice versa

Unknown said...
This comment has been removed by a blog administrator.
Arkadeep De said...

@Soumya banerjee can u share the table structure here

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.