tag:blogger.com,1999:blog-7767918376204344706.post5640344200166023290..comments2023-10-25T09:02:03.547-07:00Comments on Sukesh Marla: Eliminate Duplicate RecordsSukesh Marlahttp://www.blogger.com/profile/16619059549331221666noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-7767918376204344706.post-55119495909279791782011-09-11T06:15:18.451-07:002011-09-11T06:15:18.451-07:00delete d1
from duplicate_records d1
join duplicat...delete d1<br />from duplicate_records d1 <br />join duplicate_records d2 on d1.dupname=d2.dupname<br />and d1.id>d2.id<br /><br />This query deletes the duplicate records keeping the records with lowest ids.<br /><br />It is useful when the table contains very few records as this query is similar to a cross join and hence the complexity is greater.Vinayhttps://www.blogger.com/profile/10101246123473318259noreply@blogger.comtag:blogger.com,1999:blog-7767918376204344706.post-82894727059434105472011-08-29T05:09:00.829-07:002011-08-29T05:09:00.829-07:00Declare @table1 table (
col1 varchar(40),
col2 var...Declare @table1 table (<br />col1 varchar(40),<br />col2 varchar(40)<br />)<br /><br />Insert into @table1 <br />values('1','sai')<br />Insert into @table1 <br />values('2','suk')<br />Insert into @table1 <br />values('1','sai')<br /><br />Declare @table2 table (<br />col1 varchar(40),<br />col2 varchar(40)<br />)<br />Insert into @table2 <br />select t.col1,t.col2 from @table1 As t group by t.col1, t.col2<br /><br />delete from @table1<br /><br /><br />Insert into @table1 <br />select t.col1,t.col2 from @table2 AS t<br /><br />select * from @table1Sai Sherlekarhttps://www.blogger.com/profile/06814179051261391347noreply@blogger.comtag:blogger.com,1999:blog-7767918376204344706.post-7025338411955561482011-08-29T05:07:58.807-07:002011-08-29T05:07:58.807-07:00Declare @table1 table (
id int,
col1 varchar(40),
...Declare @table1 table (<br />id int,<br />col1 varchar(40),<br />col2 varchar(40)<br />)<br /><br />Insert into @table1 <br />values(1,'1','sai')<br />Insert into @table1 <br />values(2,'2','suk')<br />Insert into @table1 <br />values(3,'1','sai')<br /><br />Delete from @table1 where id not in (select max(t.id) from @table1 As t group by t.col1, t.col2)<br /><br />select * from @table1<br /><br />--select min(t.id) from @table1 As t group by t.col1, t.col2Sai Sherlekarhttps://www.blogger.com/profile/06814179051261391347noreply@blogger.comtag:blogger.com,1999:blog-7767918376204344706.post-74520976460445536802011-08-29T05:06:56.533-07:002011-08-29T05:06:56.533-07:00without subquery
----------------------------
Dec...without subquery<br />----------------------------<br /><br />Declare @table1 table (<br />col1 varchar(40),<br />col2 varchar(40)<br />)<br /><br />Insert into @table1 <br />values('1','sai')<br />Insert into @table1 <br />values('2','suk')<br />Insert into @table1 <br />values('1','sai')<br /><br />Declare @table2 table (<br />col1 varchar(40),<br />col2 varchar(40)<br />)<br />Insert into @table2 <br />select t.col1,t.col2 from @table1 As t group by t.col1, t.col2<br /><br />delete from @table1<br /><br /><br />Insert into @table1 <br />select t.col1,t.col2 from @table2 AS t<br /><br />select * from @table1Sai Sherlekarhttps://www.blogger.com/profile/06814179051261391347noreply@blogger.comtag:blogger.com,1999:blog-7767918376204344706.post-36111141036599185732011-08-10T11:46:36.427-07:002011-08-10T11:46:36.427-07:00SELECT DISTINCT * INTO #temp FROM Employee
TRUNCA...SELECT DISTINCT * INTO #temp FROM Employee<br /><br />TRUNCATE TABLE Employee<br /><br />INSERT INTO Employee<br />SELECT * FROM #tempPradeep Shethttps://www.blogger.com/profile/12517588692142299056noreply@blogger.com