Deleting Records Using Join

[MMishra]

Some time we need to delete data form one table on the basis of other table.
e.g.
Table1


EmpId Emp Name Emp Dept
1 Tom Admin
2 Shyam Hr
3 Mall Admin
4 Tony Production
5 Kumar Hr

Table2

EmpId Month Salary
1 Jan 18018
1 Feb 12259
1 Mar 13436
2 Jan 15704
2 Feb 18586
2 Mar 17992
3 Jan 14555
3 Feb 10509
3 Mar 17478
4 Jan 18024
4 Feb 13316
4 Mar 12006

Consider above given data.
Lets say one wants to delete data from Table2 for all the employees who belongs to Admin department.
Same can be achieved using following query.


Delete Table2.*
From
Table2 Inner Join Table1
On Table2.EmpId=Table1.EmpId
Where Table1.[Emp Dept]='Admin'

Note:EmpId column of Table1 must have uniqueindex (no duplicate). it can be set in design mode of table, there is property named "Indexed", set it Yes(No duplicates)