Hi guys,
This time our task is to remove or eliminate duplicate entries from a sql Table.
Consider we have a Table Say Employee which contain some duplicate entries,and we have to eliminate the duplicate ones.
So lets start thinking..
Subscribe to:
Post Comments (Atom)
SELECT DISTINCT * INTO #temp FROM Employee
ReplyDeleteTRUNCATE TABLE Employee
INSERT INTO Employee
SELECT * FROM #temp
without subquery
ReplyDelete----------------------------
Declare @table1 table (
col1 varchar(40),
col2 varchar(40)
)
Insert into @table1
values('1','sai')
Insert into @table1
values('2','suk')
Insert into @table1
values('1','sai')
Declare @table2 table (
col1 varchar(40),
col2 varchar(40)
)
Insert into @table2
select t.col1,t.col2 from @table1 As t group by t.col1, t.col2
delete from @table1
Insert into @table1
select t.col1,t.col2 from @table2 AS t
select * from @table1
Declare @table1 table (
ReplyDeleteid int,
col1 varchar(40),
col2 varchar(40)
)
Insert into @table1
values(1,'1','sai')
Insert into @table1
values(2,'2','suk')
Insert into @table1
values(3,'1','sai')
Delete from @table1 where id not in (select max(t.id) from @table1 As t group by t.col1, t.col2)
select * from @table1
--select min(t.id) from @table1 As t group by t.col1, t.col2
Declare @table1 table (
ReplyDeletecol1 varchar(40),
col2 varchar(40)
)
Insert into @table1
values('1','sai')
Insert into @table1
values('2','suk')
Insert into @table1
values('1','sai')
Declare @table2 table (
col1 varchar(40),
col2 varchar(40)
)
Insert into @table2
select t.col1,t.col2 from @table1 As t group by t.col1, t.col2
delete from @table1
Insert into @table1
select t.col1,t.col2 from @table2 AS t
select * from @table1
delete d1
ReplyDeletefrom duplicate_records d1
join duplicate_records d2 on d1.dupname=d2.dupname
and d1.id>d2.id
This query deletes the duplicate records keeping the records with lowest ids.
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.