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 |
How would you retrieve Unique rows from table without using UNIQUE and DISTINCT keyword?
How do you delete a trigger?
What is model database? : SQL Server Architecture
What is Schema in Database?
Hi Friends, I have a table in which there are thousands of records and in city field there is NULL value for all records now i want to change that null value with distinct values in each record say delhi, bihar, agra, jaipur etc, what will be the query for that????? its not possible to update thousands of records one by one. is there any alternative ...? Plz help ... its urgent Thanx in advance
How many index keys possible for a table
How will you collect the date from current date to last older 6 days date in sql server 2005
What is the basic difference between clustered and a non-clustered index?
How can you start sql server in different modes?
What do you understand by replication in sql server?
Selet all the data from table where last name is n of employee
how to select 5 to 7 rows from a table, which contains 10 rows?