say I have something like:
select id from users where age > 21;
this yields
12
13
14
15
Now I would like the query to not just find those IDs but also do something on them, like:
(for each id found): delete from people where user_id = <the id found>;
how do I write that?
You just use a subquery:
DELETE FROM people WHERE user_id IN (SELECT id FROM users WHERE age > 21)
More information in the Postgresql documentation about subqueries.
In addition to the possibility of using a subquery, you can also use a simple JOIN in most cases depending on the cardinality of the relationship in most implementations of SQL. Unfortunately, I don't think that PostgreSQL allows for JOINs in its DELETE syntax, but they do have a USING clause:
DELETE FROM People USING Users
WHERE
People.user_id = Users.id AND
Users.age > 21;
Please let me know if that syntax doesn't work for you and I'll adjust my answer.
