Answer / dinesh kumar

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

Answer / lince

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

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.

Answer / kk

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

Answer / naren

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

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

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)

What is the use of sign function?

0 Answers  

Explain contrast amongst grouped and non-bunched records?

0 Answers  

What happens if you delete a table that is used by a view?

0 Answers  

List some of the rules that apply to creating and using a ‘view’

0 Answers  

What is coalesce and check constraint in sql server?

0 Answers  

What is sub-query in sql server? Explain its properties.

0 Answers  

What is an execution plan? When would you use it?

0 Answers  

Why de-normalization required?

0 Answers  

In performance wise distinct is good or group by is good? eg:select name from emp group by name; select distinct name from emp;

5 Answers   Infosys,

Why use triggers?

0 Answers  

Do you know how to store and query spatial data?

0 Answers  

How do database indexes work?

0 Answers