postgresql - 从删除子查询返回值中选择

Question

我正在尝试将两个步骤组合成一个查询。我试图从一个表中删除具有特定商店 ID 的行，然后在另一个表中停用员工(如果他们在第一个表中不再有任何匹配行)。这是我得到的:

UPDATE business.employee
SET active = FALSE
WHERE employee_id IN
(SELECT employee_id FROM (DELETE FROM business.employeeStore
WHERE store_id = 1000
RETURNING employee_id) Deleted
LEFT JOIN business.employeeStore EmployeeStore
ON Deleted.employee_id = EmployeeStore.employee_id
WHERE EmployeeStore.store_id IS NULL)

从逻辑上讲，我认为我写的内容是合理的，但从句法上讲，它并不完全正确。看起来这应该是可能的，因为 DELETE FROM 子查询返回结果的单个列表，并且该子查询本身可以正常工作。但它告诉我在 FROM 处或附近存在语法错误。即使我不包括查询的 UPDATE 部分，只执行内部 SELECT 部分，它也会给我同样的错误。

更新:我尝试使用 WITH 命令来解决语法问题，如下所示:

WITH Deleted AS (DELETE FROM business.employeeStore
WHERE store_id = 1000
RETURNING employee_id)
UPDATE business.employee
SET active = FALSE
WHERE employee_id IN
(SELECT employee_id FROM Deleted
LEFT JOIN business.employeeStore EmployeeStore
ON Deleted.employee_id = EmployeeStore.employee_id
WHERE EmployeeStore.store_id IS NULL)

这不会产生任何错误，但在研究代码一段时间后，我确定虽然它确实从 WITH 部分获得了结果，但实际上并没有执行 DELETE 直到 UPDATE 完成。所以 SELECT 子查询不返回任何结果。

Answer 1

我终于能够弄清楚如何使用 WITH 来做到这一点。主要问题是需要处理处于 DELETE 之前状态的表。我像这样将所有内容保存在一个查询中:

WITH Deleted AS 
    (DELETE FROM business.employeeStore
    WHERE store_id = 1000
    RETURNING employee_id)
UPDATE business.employee
SET active = FALSE
WHERE employee_id IN
    (SELECT employee_id FROM Deleted)
AND employee_id NOT IN
    (SELECT employee_id FROM Deleted
    JOIN business.employeeStore EmployeeStore
    ON Deleted.employee_id = EmployeeStore.employee_id
    WHERE EmployeeStore.store_id != 1000)