How to delete duplicate records from a table?(for suppose in
a table we have 1000 Records in that we have 200 duplicate
Records , so ,how to findout that duplicate Records , how to
delete those Records and arranged into sequence order? one
more thing that there is no primary key at all)
Answers were Sorted based on User's Feedback
Answer / sneha
select distinct * from table into new_table;
delete table;
select * from new_table into table;
Is This Answer Correct ? | 8 Yes | 6 No |
Answer / cp
DELETE FROM emp WHERE ROWID NOT IN(SELECT MAX(ROWID) FROM
emp GROUP BY empno)
Is This Answer Correct ? | 2 Yes | 3 No |
Answer / smitha
;with empctc(empid,ename,sal,deptid,ranking)
as
(Select empid,ename,sal,deptid,ranking=Dense_rank() over (
partition by empid,ename,sal,deptid order by NEWID() asc)
from emp
)
delete * from empctc where ranking>1
Is This Answer Correct ? | 2 Yes | 5 No |
Answer / sirisha
with numbered as(select rowno = row_number() over(partition
by empid order by empid),empname from employee)delete from
numbered where rowno > 1
Is This Answer Correct ? | 1 Yes | 6 No |
Answer / pooja narang
We will get the duplicate records and insert them into a
new temp table by using below query:
select * into tmp_Employee
from Employee
having count(distinct *) > 1
Now delete the duplicate records from Employee table:
delete from Employee
having count(distinct *) > 1
Now insert the records from tmp_Employee to Employee table:
insert into Employee
select * from tmp_employee
drop table tmp_employee
Is This Answer Correct ? | 4 Yes | 15 No |
How do I setup a sql server database?
What is sql collation?
What is logon trigger?
How To delete duplicate record from a particular table?
What is Deadlock?
If i am handling 150 servers then how to check the active jobs of all the servers?
What is the difference between nvl and nvl2?
What is cube dimension? : sql server analysis services, ssas
What factors you will consider calculating the storage requirement for that view?
What are views in ms sql server?
Why normalization is used?
How to stop log file growing too big?