How to use the results of a CTE query inside another query

I want to take the result of a CTE query and use it inside another query.

This simplified example uses a CTE query to return a list of ids.

with test_cte (id,name) as (
    select id, name
    from test
)
select id
from test_cte
where name = 'john'

I want to use this list of ids to delete some records like this but I'm getting a syntax error:

delete from test
where id in (
    with test_cte (id,name) as (
        select id, name
        from test
    )
    select id
    from test_cte
    where name = 'john'
)

Is there a way to do this?

from Recent Questions - Stack Overflow https://ift.tt/3lp92qR
https://ift.tt/eA8V8J