How do I delete records based on query results

Question

How to I delete records from a table which is referenced in my query for example, below is my query which returns me the correct amount of results but I then want to delete those records from the same table that is referenced in the query.

;with cte as (select *, 

row_number() over (partition by c.[Trust Discharge], c.[AE Admission], c.[NHS Number] 
                    order by c.[Hospital Number]) as Rn,

count(*) over (partition by c.[Trust Discharge], c.[AE Admission], c.[NHS Number]) as cntDups 
        from CommDB.dbo.tblNHFDArchive as c)

        Select * from cte

Where cte.Rn>1 and cntDups >1

Yes I want to delete from CommDB.dbo.tblNHFDArchive where the above criteria is met. — Simon, Nov 21 '16 at 08:41
hmm, I think what you are trying to do is answered here http://stackoverflow.com/questions/812512/faster-way-to-delete-matching-rows — am05mhz, Nov 21 '16 at 08:51

score 1 · Answer 1 · answered Nov 21 '16 at 08:45

as you can already select the rows by querying Select * from cte Where cte.Rn>1 and cntDups >1, you can delete them by running delete from your_table where unique_column in (Select unique_column from cte Where cte.Rn>1 and cntDups >1)

note that unique_column is a column in your table that cannot have duplicate values, and your_table is the table where the rows reside.

and don't forget to backup your table first if it's on production.

How do I delete records based on query results

1 Answers1