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 |
How to find the last update record in SQL Server?
Suppose in a situation if two list boxes are there and if you select multiple options based on that the options related to those selected items should display in second list box. Again if we select multiple items in second listbox then the related to those selected items should display . In this scenario how will you design database,tables?
Is mysql better than sql server?
What is difference between createstatement and preparedstatement?
What is the difference between char, varchar and nvarchar?
what is the difference between a primary key and a unique key? : Sql server database administration
What is full outer join in sql server joins?
Explain transaction server implicit?
Explain unique key in sql server?
What the class forname () does?
What is the data type of time?
what is the difference between Delete and Truncate command in SQL