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

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?

Here's my real world case, I want to delete from script_covid_billing_temp:

with maxdate_cte(facilitynumber,account,lastservicedate,logtype) as 
(select 
s.facilitynumber,
s.account,
max(lastservicedate) as lastservicedate,
(select top 1 logtype from script_covid_billing 
where facilitynumber=s.facilitynumber and account=s.account and 
logtype in ('bill_cancel','bill_cancel_error')
order by lastservicedate) as logtype
from script_covid_billing s
group by s.facilitynumber,s.account)

select id 
from script_covid_billing_temp t
left join maxdate_cte m on m.facilitynumber=t.facilitynumber and m.account=t.account
where (t.lastservicedate<m.lastservicedate) or 
(t.lastservicedate=m.lastservicedate and m.logtype='bill_cancel')

CodePudding user response：

Do you just mean this:

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

Do you want to delete rows and show the rows you deleted?

;with test_cte(id,name) as
(
  select id,name from dbo.test
)
delete test_cte 
output deleted.id, deleted.name
where name='john';

Example db<>fiddle

To take your explicit example:

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'
)

You're getting a syntax error because, well, there's an error in your syntax. CTE must be defined up front, not in any random or arbitrary point in your query.

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

But this still seems awfully over-complicated compared to the simpler examples I've shown.