So today our missions is to delete all employee records if in the title of their department can be found word "sales". It can happen in the real life. Imagine that newly elected CEO decides to close all sales operations.

MS Access syntax will be:

1. The well known way, that uses sub-query:



DELETE *
FROM Employees
WHERE DeptNo IN
  (SELECT DeptNo
  FROM Departments
  WHERE LCase(DeptName) LIKE '*sales*')



2. The more efficient way is to use "Delete Join":



DELETE a.*
FROM Employees AS a INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo
WHERE LCase(b.DeptName) LIKE '*sales*'



3. Same query ("Delete Join") on SQLServer 2005:



DELETE a
FROM Employees AS a INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo
WHERE LOWER(b.DeptName) LIKE '%sales%'


4. Abstruct query ("Delete Join") on SQLServer 2005/2008:
Suppose that we have two tables. Fitting numbers in id column are marked by green color.



Table1
Table2
id
ItemName
1
item1
2
item2
4
item4
5
item5
id
2
3
5
9



Following delete statement will remove records with id 2 and 5 from Table1:



DELETE a
FROM Table1 AS a INNER JOIN Table2 AS b
ON a.id = b.id 


Important Key Words :

How to delete records from one table using join with another table
Delete records from one table using join with another table
Delete records using join queries
Delete records join query