CODEWITHSUNDEEP
sqlpostgresql
JasonGenX
JasonGenX

Reputation: 5434

postgres; query to work on a set of IDs found

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?

Upvotes: 0

Views: 145

Answers (2)

Tom H
Tom H

Reputation: 47464

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.

Upvotes: 0

jcaron
jcaron

Reputation: 17710

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.

Upvotes: 3

Related Questions