当前位置: 动力学知识库 > 问答 > 编程问答 >

SQL Server : delete duplicate rows without Unique ID

问题描述:

I have table and it has these columns:

pkID int

Title varchar(255)

FromEmail varchar(500)

ToEmail varchar(500)

CCEmail varchar(500)

BCCEmail varchar(500)

Subject varchar(500)

EmailBody ntext

EmailFields varchar(5000)

and my table has 4 rows data same value as below

pkID 44

Title 'Notification'

FromEmail NULL

ToEmail NULL

CCEmail NULL

BCCEmail NULL

Subject 'Send Email for Notification'

EmailBody NULL

EmailFields NULL

My table name is EmailTemplates

If I try this query:

;WITH cte

(

SELECT

*, ROW_NUMBER() OVER (ORDER BY pkID) RN

FROM EmailTemplates

WHERE pkID = 44

)

DELETE FROM cte

WHERE RN > 1

I get this error:

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near ')'.

I want to delete pkID equals to 44 it will delete 4 rows so how can I delete 3 rows pkID equals 44 ?

Thanks.

网友答案:

Use CTE with row_number to delete the duplicates

;with cte as
(
select *,row_number() over(order by pkID) RN 
FROM yourtable
where pkID = 44
)
delete from cte where RN>1

Note: In order by you can mention the in which order you want to delete the duplicates

网友答案:

You can use a CTE and ROW_NUMBER. It allows to change it easily to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE... to SELECT * FROM CTE:

WITH CTE AS(
   SELECT e.*,
       RN = ROW_NUMBER() OVER (PARTITION BY pkID ORDER BY pkID)
   FROM dbo.EmailTemplates e
   WHERE pkID = @pkID -- if you want to delete only a specific ID
)
DELETE FROM CTE WHERE RN > 1 

If you remove WHERE pkID = @pkID you will delete all duplicates by always keeping one row per pkID. So you need to change ORDER BY pkID to something different if you want to change the logic to something more meaningful, currently it's arbitrary.

If you want to take multiple columns into account, you just need to change PARTITION BY pkID to (f.e.) PARTITION BY pkID, Title.

分享给朋友:
您可能感兴趣的文章:
随机阅读: