duplicate records

enrique_aeo
enrique_aeo used Ask the Experts™
on
Hello, I have a table with over a million records. Need
1. Show only duplicate records (column id)
2. Eliminate duplicate records
I hope you can help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 1. Show only duplicate records (column id)

If you want to get the duplicate records alone, then

SELECT column_id
from ur_table
group by columns_list
having count(*) > 1

2. Eliminate duplicate records

with cte as (
SELECT column_id, row_number() over ( partition by columns_list order by some_column) rnum
from ur_table
group by columns_list
having count(*) > 1)
delete from cte
where rnum > 1

Posting your table structure would help providing the correct query with column names replaced..

Author

Commented:
CREATE TABLE duplicatedRedord
(
DocID int NOT NULL,
Title varchar(200) NOT NULL,
Specification char(500) NULL,
Location smallint NULL,
SurveyGroup char(500) NULL
) ;
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Do you have any unique columns or primary key..
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
It is an import data from oracle. To avoid any error occurs you work the table without primary key
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
If you don't have Primary or Unique key columns, then

select DocID, Title, Specification,Location,SurveyGroup
FROM duplicatedRedord
group by DocID, Title, Specification,Location,SurveyGroup
having COUNT(*) >1

-- To delete

with cte as (
select DocID, Title, Specification,Location,SurveyGroup, ROW_NUMBER() over ( partition by DocID, Title, Specification,Location,SurveyGroup
order by DocID) rnum
FROM duplicatedRedord
group by DocID, Title, Specification,Location,SurveyGroup
having COUNT(*) >1)
delete from cte
where rnum > 1

Author

Commented:
i execute the second query. I get this error
Msg 4403, Level 16, State 1, Line 1
Cannot update the view or function 'cte' because it contains aggregates, or a DISTINCT or GROUP BY clause, or PIVOT or UNPIVOT operator.
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
small mistake:

with cte as (
select DocID, Title, Specification,Location,SurveyGroup, ROW_NUMBER() over ( partition by DocID, Title, Specification,Location,SurveyGroup
order by DocID) rnum
FROM duplicatedRedord)
delete from cte
where rnum > 1

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial