duplicate records

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
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
enrique_aeoAuthor Commented:
CREATE TABLE duplicatedRedord
(
DocID int NOT NULL,
Title varchar(200) NOT NULL,
Specification char(500) NULL,
Location smallint NULL,
SurveyGroup char(500) NULL
) ;
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Do you have any unique columns or primary key..
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

enrique_aeoAuthor Commented:
It is an import data from oracle. To avoid any error occurs you work the table without primary key
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
0
enrique_aeoAuthor 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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.