Delete duplicate records from the table?(Table must have
unique id)
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
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 |
What should be the fill factor for indexes created on tables? : sql server database administration
What is the optimization being performed in oracle and SQL Server?
How many types of the database links?
What is SQL Server?
write an SQL query to list the employees who joined in the month of January?
What is the use of partition by in sql server?
Can a rule be bound to any column of any data type?
Define left outer join?
What are the ways available in sql server to execute sql statements?
When you first load SQL SERVER you will startup with what all databases?
What is a document index?
What is difference between after and before?