|
|
Ok, the mission 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 newlly 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%'
|
|