How to delete records from one table using join with another table ?
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:
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*')
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*'
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%'
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
|
|||||||||||||||
|
|
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
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
Delete records from one table using join with another table
Delete records using join queries
Delete records join query
No comments:
Post a Comment