mysql - Delete Using Multiple Tables and repeat table in the subquery

Question

I want fire all employees from the department with lower sales

Schema and Sql fiddle

CREATE TABLE Employee
    (`ID` int, `name` varchar(6), `deptID` int);

INSERT INTO Employee
    (`ID`, `name`, `deptID`)
VALUES
    (1, 'Jhon', NULL),       (2, 'Luis', 1),
    (3, 'Angela', 1),        (4, 'Peter', NULL),
    (5, 'Sonia', 4),         (6, 'Oliver', 4);

CREATE TABLE Sales
    (`ID` int, `Sales` int);

INSERT INTO Sales
    (`ID`, `Sales`)
VALUES
    (1, 100),        (2, 300),
    (3, 500),        (4, 600),
    (5, 250),        (6, 150);

I can do things like this

DELETE E 
FROM Employee E
INNER JOIN Sales S 
   ON E.`ID` = S.`ID`
WHERE `SALES` = 600;

What I want

DELETE E1 
FROM Employee E1
WHERE `deptID` IN (
            SELECT `deptID`
            FROM Employee E 
            Inner JOIN Sales S
               ON E.`ID` = S.`ID`
            GROUP BY `deptID`
            HAVING SUM(`Sales`) <= 400
        );

But I can't use Employee in the inside SELECT as describe on the manual

Subqueries
Currently, you cannot delete from a table and select from the same table in a subquery.

So what is the correct syntaxis or workaround?

Answer 1

Use a JOIN

DELETE e1
FROM Employee AS e1
JOIN (SELECT deptID
      FROM Employee AS e
      JOIN Sales AS s ON e.ID = s.ID
      GROUP BY deptID
      HAVING SUM(Sales) <= 400) AS d
ON e1.deptID = d.deptID