Select from a delete subquery returning values

Question

I'm trying to combine two steps into a single query. I'm trying to remove rows from one table with a particular store ID, and then deactivate employees on another table if they no longer have any matching rows in the first table. Here's what I've got:

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)

Logically, I think what I've written is sound, but syntactically, it's not quite there. It seems like this should be possible, since the DELETE FROM subquery is returning a single column table of results, and that subquery works fine by itself. But it tells me there is a syntax error at or near FROM. Even if I don't include the UPDATE portion of the query, and just do the interior SELECT part, it gives me the same error.

UPDATE: I tried using a WITH command to get around the syntax problem as follows:

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)

This doesn't produce any errors, but after playing around with the code for a while, I've determined that while it does get the results from the WITH part, it doesn't actually do the DELETE until after the UPDATE completes. So the SELECT subquery doesn't return any results.

Answer 1

I finally was able to work out how to do this using the WITH. The main issue was needing to handle the table in its pre-DELETE state. I've kept it all in one query like so:

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)