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.

It's the DELETE FROM that has the error. I got partway to a solution and am about to edit my question. — Joe M
– Joe M, Commented Nov 12, 2019 at 19:23

Joe M · Accepted Answer · 2019-11-12 21:43:41Z

3

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)

answered Nov 12, 2019 at 21:43

Joe M

3,5904 gold badges45 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

luukvhoudt Over a year ago

Thank you very much! This helped me to create a stored procedure that can delete and return data where after this data is used to run an extensive select query where of the results get returned by the procedure. Check it out here in action!

Collectives™ on Stack Overflow

Select from a delete subquery returning values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related