Using a CTE in a Delete Statement SQL Server 2008 r2

Question

I have a CTE which pares down a set of people based on their ID numbers. I don't have a good way to test this at the moment. I haven't used a CTE in conjunction with a delete statement before, I think this is right but I'd like to proceed after I'm sure.

I did test this and I got the following error:

(0 row(s) affected)
Msg 208, Level 16, State 1, Line 35
Invalid object name 'x'.

What am I doing wrong here?

--this CTE pares down the number of people that I need for my final result set
;with x as (select distinct patid from
(
select distinct patid
    from clm_extract
    where (diag1 like '952%' or diag1 like '806%') and drg =444

union
select distinct patid
    from clm_extract
    where (diag2  like '952%' or diag2 like '806%') and drg =444

union
select distinct patid
    from clm_extract
    where (diag3  like '952%' or diag3  like '806%') and drg =444
union
select distinct patid
    from clm_extract
    where (diag4  like '952%' or diag4  like '806%') and drg =444
union
select distinct patid
    from clm_extract
    where (diag5  like '952%' or diag5  like '806%') and drg =444
) x

)
--this is a query to show me the list of people that I need to delete from this table because they do not match the criteria
select distinct x.patid
    from x
    inner join clm_extract as c on c.patid = x.patid
    where x.patid !=1755614657 (1 person did match)

--this is my attempt at using a CTE with said query to remove the IDs that I don't need from my table

delete from clm_extract
where patid in(select distinct x.patid
    from x
    inner join clm_extract as c on c.patid = x.patid
    where x.patid !=1755614657)

Well you can take all the distincts out of there for starters, and why can't you test it? — Tony Hopkinson
– Tony Hopkinson, Commented Oct 16, 2012 at 17:27
I wasn't at a terminal where that could be easily done. I since moved, tested and and the error message is in the question. — wootscootinboogie
– wootscootinboogie, Commented Oct 16, 2012 at 17:28

marc_s · Accepted Answer · 2012-10-16 17:28:29Z

2

I think your CTE is wrong - you have a CTE called x and inside that CTE you have a subselect also aliassed to be x - this is causing confusion...

Why not just have:

;with x as 
(
    select distinct patid
    from clm_extract
    where (diag1 like '952%' or diag1 like '806%') and drg =444

    union

    select distinct patid
    from clm_extract
    where (diag2  like '952%' or diag2 like '806%') and drg =444

    ......     
)
select 
    distinct x.patid
from x
inner join clm_extract as c on c.patid = x.patid
where x.patid !=1755614657 (1 person did match)

I don't see any need nor any benefit in having that extra subquery inside your CTE, really ....

answered Oct 16, 2012 at 17:28

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Using a CTE in a Delete Statement SQL Server 2008 r2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related