删除使用多个表并在子查询中重复表 [英] Delete Using Multiple Tables and repeat table in the subquery

问题描述

我希望解雇该部门所有销售额较低的员工

I want fire all employees from the department with lower sales

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);

我可以做这样的事情

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

我想要的

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
        );

但是我不能按照 Employee rel ="nofollow">手册

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?

推荐答案

使用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

这篇关于删除使用多个表并在子查询中重复表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！