Delete duplicate records from the table?(Table must have unique id)

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

Delete duplicate records from the table?(Table must have
unique id)

Question Posted / brijesh darmwal, sandhya

7 Answers
10271 Views
TCS, Thomson Reuters, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Delete duplicate records from the table?(Table must have unique id)..

Answer / dinesh kumar

delete from emp where id Not in(select max(id) id from emp
group by name having count(id)>1)

Is This Answer Correct ?

22 Yes

9 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / lince

DELETE
FROM MyTable
WHERE ID IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1 HAVING COUNT(ID)>1
)

Is This Answer Correct ?

4 Yes

2 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / manoj

Check following blog post on how to first identify & then delete duplicate records: http://sqlwithmanoj.wordpress.com/2011/10/14/identify-delete-duplicate-records-from-a-table/

This lists multiple ways to delete duplicates.

Is This Answer Correct ?

2 Yes

0 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / kk

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Is This Answer Correct ?

7 Yes

7 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / naren

delete id from table where id in(select id from table having count(id)>1)

Is This Answer Correct ?

1 Yes

2 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / krishna

select *
from mytable b
join
(
select Duplicatecolumn,MAX(id) id
from mytable
group by Duplicatecolumn
) b1 on b.Duplicatecolumn = b1.Duplicatecolumn
where b.id <> b1.id

Is This Answer Correct ?

0 Yes

1 No

Delete duplicate records from the table?(Table must have unique id)..

Answer / brijesh darmwal, sandhya

DELETE FROM [MyDb].[dbo].[sandhya]
WHERE id
IN
(SELECT id
FROM
(SELECT MAX(id) as id,name,addr
FROM [MyDb].[dbo].[sandhya] GROUP BY name,addr having
count(id)>=2)
Tmp)

Is This Answer Correct ?

7 Yes

16 No

Post New Answer

More SQL Server Interview Questions

What is the most common type of join?

0 Answers

In the below query i have performed the commit transaction statement but still the values after the save are not saved. Can you please let me know why are the statements after save are rolled back even after commiting the data. help me with the understanding declare @trans2 varchar(10)='transaction2' begin transaction @trans2 insert into emp values(100,'xy',600); save transaction @trans2 insert into emp values(200,'pq',700); insert into emp values(300,'pq',800); commit transaction @trans2 rollback tran @trans2

1 Answers AllState,

What do you understand by the analysis services in sql server?

0 Answers

How many triggers are possible per table?

0 Answers

how to find the second salary?

10 Answers HCL, Prithvi,

how we can count records as a group of days like sum of records for(four mondays),(four tuesday)........ in a month. group the column for weekdays.

1 Answers

what is a default tcp/ip socket assigned for sql server? : Sql server database administration

0 Answers

Why I am getting "the microsoft .net framework 2.0 in not installed" message?