我正在尝试将两个步骤组合成一个查询。我正在尝试从一个具有特定商店ID的表中删除行,然后停用另一个表上的员工(如果他们在第一个表中不再有匹配的行)。这就是我所拥有的:
UPDATE business.employee
SET active = FALSE
WHERE employee_id IN
(SELECT employee_id FROM (DELETE FROM business.employeeStore
WHERE store_id = 1000
RETURNING user_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 user_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
部分得到了结果,但实际上并没有做{ {1}},直到DELETE
完成为止。因此,UPDATE
子查询不会返回任何结果。