if 3 duplicate records in a table,i want to delete 2 duplicate records by keep

Categories >> Software >> Databases >> SQL Server
Suggest New Category

if 3 duplicate records in a table,i want to delete 2 duplicate
records by keeping 1 duplicate and 1 original as it is,how?

Question Posted / jagan

5 Answers
7230 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

if 3 duplicate records in a table,i want to delete 2 duplicate records by keeping 1 duplicate and ..

Answer / mohamed zunu

create table sampletbl(id int,name varchar(100))

insert into sampletbl values(1,'aa')
insert into sampletbl values(1,'aa')
insert into sampletbl values(1,'aa')

with cte as(
select ROW_NUMBER() over (partition by id order by id) as r_no,* from sampletbl)
delete from cte where r_no>1
select * from sampletbl

Is This Answer Correct ?

9 Yes

1 No

if 3 duplicate records in a table,i want to delete 2 duplicate records by keeping 1 duplicate and ..

Answer / siva raman

By using Rank function we can delete duplicate records in
tables.

Is This Answer Correct ?

6 Yes

3 No

if 3 duplicate records in a table,i want to delete 2 duplicate records by keeping 1 duplicate and ..

Answer / amol maske

DELETE FROM tablename WHERE ROWID NOT IN(
SELECT MIN(ROWID)FROM tablename GROUP BY columnname);

Is This Answer Correct ?

5 Yes

4 No

if 3 duplicate records in a table,i want to delete 2 duplicate records by keeping 1 duplicate and ..

Answer / dhananjay

The simplest way to eliminate the duplicate records is to
SELECT DISTINCT into a temporary table, truncate the
original table and SELECT the records back into the original
table. That query looks like this:

select distinct *
into #holding
from dup_authors

truncate table dup_authors

insert dup_authors
select *
from #holding

drop table #holding

Is This Answer Correct ?

0 Yes

0 No

if 3 duplicate records in a table,i want to delete 2 duplicate records by keeping 1 duplicate and ..

Answer / arun ashok

STEP 1 : Insert distinct value in the one new table.
STEP 2 : Delete the values from existing table.
STEP 3 : Again insert the values from new table to existing
table.

Is This Answer Correct ?

0 Yes

1 No

Post New Answer