How To delete duplicate record from a particular table?
Answers were Sorted based on User's Feedback
Answer / kala suresh
--BY USING A TEMP TABLE TO DELETE DUPLICATE ROWS OF A TABLE
WITH OUT A PRIMARY KEY COLUMN OR AN IDENTITY COLUMN
SELECT Name, age, ROW_NUMBER() OVER(ORDER BY age) AS
'RowNumber' into #temp1 FROM RowNumber
delete from #temp1 where rownumber not in(select
max(rownumber) from #temp1 group by name,age)
delete from rownumber
select * from #temp1 into rownumber
drop table #temp1
--BY USING AN IDENTITY COLUMN TO DELETE DUPLICATE ROWS OF A
--TABLE WITH OUT A PRIMARY KEY COLUMN
delete from rownumber where id not in(select min(id) from
rownumber group by name,age)
select * from rownumber
Is This Answer Correct ? | 4 Yes | 1 No |
Mr. Parashu, Ur query will delete all dup rec at all. the
solution should be for del dups but keepning single record.
understand?? :)
Is This Answer Correct ? | 4 Yes | 2 No |
Answer / srinivasan
WITH CTE AS
(
SELECT * ,ROW_NUMBER() OVER (PARTITION BY ID,NAME
ORDER BY ID DESC) AS RNUM FROM TABLE A
)
DELETE CTE WHERE RNUM >1
IF U HAVE ANY MORE DOUBTS
MAIL ME MCAVASAN@GMAIL.COM
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / manoj
DELETE FROM table_name a
WHERE ROWID >(SELECT min(ROWID)
FROM table_name b
WHERE a.col_1=b.col_1 )
Is This Answer Correct ? | 6 Yes | 6 No |
Answer / pavan kumar
Mr. Manoj, the system throws error message as "Incrrect
sysntax near 'a'"
Is This Answer Correct ? | 3 Yes | 3 No |
Answer / g2
create table Table1(trowid int not null, tname varchar(100))
insert into table1(trowid, tname) values(1, 'G2')
go 100
declare @row int;
set @row= (select count(*) from table1)
set @row=@row-1
set rowcount @row
delete from table1 -- You can put here conditions with all
the columns also
set rowcount 0
go
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / arif jameel
ADD a new identity_column (1,1) on <table>
by
alter table <table_name>
add <Identity_column> int identity(1,1)
delete from <table> where <identity_Column> in
(select max(<identity_Column>) from <table>
group by any <table_column_name>)
drop <identity_column>
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / jagan mohan varma
employee
----------------------------
eid ename sal
----- --------- ------
1 jagan 2000
2 mohan 3000
3 varma 4000
********************************
attendence
-------------------------------
id eid date
----------- ----------- -----------------------
10 1 2010-06-10 00:00:00.000
11 1 2010-06-10 00:00:00.000
12 2 2010-06-10 00:00:00.000
13 2 2010-06-10 00:00:00.000
14 3 2010-06-10 00:00:00.000
15 3 2010-06-10 00:00:00.000
16 3 2010-06-10 00:00:00.000
*************************************************
Deleting duplicate records from attendence table could be:
**********************************************************
delete from attandence
where id not in (
select max(atd.id)
from employee emp
inner join attandence atd
on atd.eid = emp.eid group by atd.eid)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / sudhagar
Delete from (select * from <TABLE_NAME> where rowid not in
(select min(rowid) from <TABLE_NAME> group by c1,c2...))
Is This Answer Correct ? | 3 Yes | 4 No |
Answer / anil sharma
In sqlserver 2000 we must use a unique id per row then we
can delete duplicate rows.
delete from <tablename> where <rowid> not in (select min
(<rowid> from <tablename> group by co1,col2.)
But in sqlserver 2005 there a function RowId.Which is same
as above concept.It return unique id per row.
Is This Answer Correct ? | 1 Yes | 2 No |
IF more than one Site is accessing the same Database server and I want to move the DB with Minimum down time? How will you do
What is single-user mode and what are the steps you should follow to start sql server in single-user mode?
What should we do to copy the tables, schema and views from one SQL Server to another?
What are .mdf files?
What is a partition function in sql server?
What is log in sql server?
Define self join in sql server joins?
What is the difference between functions and stored procedures?
Can you explain some of the DTS problems?
What do you mean by the term 'normalization'?
Explain primary key?
Explain what is the use of custom fields in report?