Two exactly same rows- want to delete only one | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Two exactly same rows- want to delete only one

I have a table in which there are two records that are exactly the same. I want to delete only one of those records…
How do I do it in SQL Query Analyser ? eg: Records are 1|sitanshu|male|working|IT
1|sitanshu|male|working|IT

2|sita|female|nonworking|NA I want to delete any one these striked records.
Sitanshu Gupta
check this http://www.sql-server-performance.com/dv_delete_duplicates.asp
http://www.sql-server-performance.com/rd_delete_duplicates.asp
This is a frequent question in this forum. check this article
http://www.sql-server-performance.com/rd_delete_duplicates.asp —————————————-
http://spaces.msn.com/members/dineshasanka

Select identity(int, 1,1) as id,* into #t from yourtable
Go
Truncate table yourTable
Go
Insert into yourTable(columns)
select columns from #t
where id in(select max(id) from #t group by col)
Go
Drop table #t Madhivanan Failing to plan is Planning to fail
Or:
create table #t(…) –the same structure as original one
insert into #t
select col1, col2, col3, col4, col5
from table
group by col1, col2, col3, col4, col5
having count(*) > 1 begin transaction delete from t1
from table t1
join #t t2 on t1.col1 = t2.col1 and … and t1.col5 = t2.col5 if @@error <> 0 rollback transaction
else begin
insert into table(…)
select *
from #t if @@error <> o rollback transaction
else commit transaction
end
or set rowcount 1
delete from tablex where pkid = 1
set rowcount 0 Cheers
Twan
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Twan</i><br /><br />or<br /><br />set rowcount 1<br />delete from tablex where pkid = 1<br />set rowcount 0<br /><br />Cheers<br />Twan<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thats correct although specific to the given data only [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
very true it only solves the specific problem above, but it is nice and simple <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />I like simple, means I can understand it ;-0<br /><br />Cheers<br />Twan
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Sitanshu</i><br /><br />I have a table in which there are two records that are exactly the same. I want to delete only one of those records…<br />How do I do it in SQL Query Analyser ?<br /><br />eg:<br /><br />Records are<br /><br /><s>1|sitanshu|male|working|IT<br />1|sitanshu|male|working|IT</s><br />2|sita|female|nonworking|NA<br /><br />I want to delete any one these striked records.<br /><br /><br />Sitanshu Gupta<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />…and after you’ve finished this data cleansing, you are going to implement a PRIMARY KEY, right? [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
or at least Identity, timestamp,etc Madhivanan Failing to plan is Planning to fail
]]>