delete Duplicate Row SQL server

Question

I am using SQL Server . i have creating SQL table have more than 20000 lines. i have Filter duplicate Using Following Query.

SELECT
    Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate,COUNT(*)
FROM
    TotalsByGLCenter
GROUP BY
    Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate
HAVING 
    COUNT(*) > 1

now I want to delete Duplicate from SQL Server How to add delete Following above Query ?

All the duplicate entries or do you want to retain one of them? — P.Salmon, Sep 27 '16 at 08:34
See this link http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — Balan, Sep 27 '16 at 08:56

Mitz · Accepted Answer · 2016-09-27T09:29:29.027

6

;WITH cte
         AS (Select ROW_NUMBER() OVER  (PARTITION BY Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate ORDER BY ( SELECT 0)) RowNum
             FROM   TotalsByGLCenter)
    Delete FROM cte
    WHERE  RowNum > 1;

The “ROW_NUMBER()” in the above query returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. The “ORDER BY” clause determines the order in which the ROW_NUMBER value is assigned to the rows in a partition. The “PARTITION BY” clause used here to divide the result set into partitions;

The new column RowNum shows row numbers of the duplicate rows.

In case if you want to see the duplicate rows you can use select instead of delete

   ;WITH cte
         AS (Select ROW_NUMBER() OVER  (PARTITION BY Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate ORDER BY ( SELECT 0)) RowNum,Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate 
             FROM   TotalsByGLCenter)
    Select * FROM cte
    WHERE  RowNum > 1;

edited Sep 27 '16 at 09:29

answered Sep 27 '16 at 08:54

Mitz

561
8
21

@P.Salmon if you are not clear with the answer pls look go through this article http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/ – Mitz Sep 27 '16 at 09:03
The link does not explain how it works.Can you explain or provide the appropriate link? what happens if there is more than one table in select statement? – xyz Sep 27 '16 at 09:21
1

sorry @thenna there was a comma added by mistake. Check now – Mitz Sep 27 '16 at 09:32
The link does not say how deleting from CTE deletes from actual table – xyz Sep 27 '16 at 09:32
This could explain, i think: https://learn.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql notes that with-subquery must be "updatable and reference exactly one base table in the FROM clause of the view definition. For more information about updatable views, see CREATE VIEW (Transact-SQL) (https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql)." – Nashev Apr 06 '18 at 09:06

P.Salmon · Answer 2 · 2016-09-27T09:48:41.740

0

Here's a (simplified) example

drop table t
create  table t (id int)

insert into t values (1),(1),(1),(2),(3),(3)

;WITH CTE AS(
  select id,count(*) as dupes
  from t
  group by t.id
  having count(*) > 1

)
DELETE  t where t.id  in (select  cte.id from cte)

result

1> select * from t
2> ;
3> go
id
-----------
          2

edited Sep 27 '16 at 09:48

answered Sep 27 '16 at 09:33

P.Salmon

17,104
2
12
19

score 0 · Answer 3 · answered Sep 28 '16 at 08:13

0

you could also create a new regular table with the same structure, insert distinct data in that table, then delete your original table and rename new table to the same name as original table had

answered Sep 28 '16 at 08:13

Jason Clark

1,307
6
26
51

delete Duplicate Row SQL server

3 Answers3