rahultripathi

 Profile Answers by rahultripathi 

• Sep 20th, 2005

 

delete from emp where en not in (select min(en) from emp order by columnname)

Srinivas.N

• Oct 4th, 2005

 

delete from <tablename> where rowid  <  (select max(rowid) from <tablename> where <tablename>.<columnname>=<tablename>.<columnname>)

XYZ

• Oct 5th, 2005

 

Using DISTINCT statement

aruna

• Apr 3rd, 2006

 

by using Distinct with select statement

suchitra

• Apr 4th, 2006

 

delete from emp where rowid not in(select max(rowid) from emp group by empno)

ankur.jain

 Profile Answers by ankur.jain 

• May 27th, 2006

 

Hi,

DELETE FROM table_name
WHERE col_name IN ( SELECT col_name FROM table_name GROUP BY col_name HAVING Count(col_name)>1)

DELETE FROM emp WHERE ename IN (SELECT ename FROM emp GROUP BY ename HAVING Count(ename)>1)

Fazlur Rahiman

• Jul 4th, 2006

 

SQL Server

--Example of Simple Duplicate Records Deletioin

-- To Create A Table Structure
Create Table Emp
(
 EmpNo Int,
 EmpName Varchar(20),
 Address Varchar(20),
 City Varchar(20)
)

--Insert the Emp Table in Values

Insert Into Emp Values(1,'Fazlur Rahiman','Al Maktum St','Dubai')
Insert Into Emp Values(2,'Kalyan','Broad Way','Chennai')
Insert Into Emp Values(3,'Vijay','Mount Road','Chennai')
Insert Into Emp Values(4,'Kumar','Parys','Chennai')
Go

-- To Insert a Duplicate Records
Insert Into Emp Values(1,'Fazlur Rahiman','Al Maktum St','Dubai')
GO

--Create temp table to hold duplicate Table (#EmpTemp)
Create Table #EmpTemp
(
 EmpNo Int,
 EmpName Varchar(20),
 Address Varchar(20),
 City Varchar(20)
)

--Identify and save duplicate data into #EmpTemp
Insert Into #EmpTemp
Select * From Emp
Group By EmpNo,EmpName,Address,City
Having Count(*) > 1

--Delete duplicate from original table
Delete From Emp Where
Ltrim(Rtrim(EmpNo))+Ltrim(Rtrim(EmpName))+LTrim(Rtrim(Address))+LTrim(Rtrim(City)) in (
Select Ltrim(Rtrim(EmpNo))+Ltrim(Rtrim(EmpName))+LTrim(Rtrim(Address))+LTrim(Rtrim(City)) From emp
Group By EmpNo,EmpName,Address,City
Having Count(*) > 1)

--Insert the delete data back
Insert Into Emp
Select * From #EmpTemp

--Check for dup data.
Select * From Emp
Group By EmpNo,EmpName,Address,City
Having Count(*) > 1

--Check table
Select * From Emp

--Drop temp table
Drop Table #EmpTemp

narender

• Nov 1st, 2006

 

 Select distinct column_name from table_name

jayaprakash.raman

 Profile Answers by jayaprakash.raman 

• Apr 24th, 2007

 

Try out the following example which demonstrates how to delete the duplicate records in Ms Sql Server.

create table dup(id int, name varchar(50))
insert into dup values (1,'raja')

insert into dup values (1,'raja')

insert into dup values (2,'krishna')

insert into dup values (2,'krishna')

insert into dup values (3,'ramana')

insert into dup values (4,'prakash')
select * into duptemp from dup
delete from duptemp where id in

(select id from dup group by id having count(id)>1)
insert into duptemp

select distinct * from dup group by id,name having count(id)>1
delete from dup

insert into dup select * from duptemp
drop table duptemp
select * from dup


Regards,
Jayaprakash Raman

KAR

• May 7th, 2007

 

If you execute this code,all the datas will be lost.If id 1&2 are repeating means,

select id from dup group by id having count(id)>1) ===Query returns 1&2.

delete from duptemp where id in

(select id from dup group by id having count(id)>1) ==Query deletes all records of 1&2.
This is not the solution .Execute the code in your sqlserver before you post.

I do see lots of errors/wrong answers/multiple answers for the same questions.Moderators has to take care of these.

Thanks
Katie

Jayaprakash

• May 8th, 2007

 

You have seen the code half way and writing the comments that it will delete all rows 1 & 2. Yes, it will delete all rows of 1 & 2 from duptmp ie temporary table. Further you see the remaining steps. For your information, i posted again the script WITH COMMENTS. Go through the comments and be clarify yourself.
IT IS TESTED. EXECUTE LINE BY LINE BY SEEING THE COMMENTS PROVIDED.

use testdb;

--creates original table dup

create table dup(id int, name varchar(50))

--insert some values

--Our intention to delete the duplicate id's:

insert into dup values (1,'raja')

insert into dup values (1,'raja')

insert into dup values (2,'krishna')

insert into dup values (2,'krishna')

insert into dup values (3,'ramana')

insert into dup values (4,'prakash')

insert into dup values (1,'kanna')

insert into dup values (2,'radha')

--select all rows with duplicate id's

select * from dup

--copy all original table values to temp table

select * into duptemp from dup

--deletes the duplicate records only

delete from duptemp where id in


(select id from dup group by id having count(id)>1)

--insert distinct records, no duplicates

insert into duptemp


select distinct * from dup group by id,name having count(id)>1

--empties the original table

delete from dup


--inserts unique records from tmep table to original table

insert into dup select * from duptemp

--task over, temp table not required

drop table duptemp

--select distinct values/ unique values/no duplicate ids from dup table

select * from dup

Sumit Ganguli

• Jun 5th, 2007

 

SET ROWCOUNT 1

DELETE TABLE_NAME FROM TABLE_NAME S1 WHERE(SELECT COUNT(*) FROM TABLE_NAME S2
WHERE S1.FIRST_FIELD_NAME=S2.FIRST_FIELD_NAME AND S1.SECOND_FIELD_NAME=S2.SECOND_FIELD_NAME)>1

WHILE @@ROWCOUNT>0

DELETE TABLE_NAME FROM TABLE_NAME S1 WHERE(SELECT COUNT(*) FROM TABLE_NAME S2
WHERE S1.FIRST_FIELD_NAME=S2.FIRST_FIELD_NAME AND S1.SECOND_FIELD_NAME=S2.SECOND_FIELD_NAME)>1

SET ROWCOUNT 0

Joby John

• Jun 14th, 2007

 

Hai,

There is one more simple solution for this problem. You can refer the following :

--creates original table dup
create table dup (id int, name varchar(50))

--insert some values
--Our intention to delete the duplicate id's:
insert into dup values (1,'raja')
insert into dup values (1,'raja')
insert into dup values (2,'krishna')
insert into dup values (2,'krishna')
insert into dup values (3,'ramana')
insert into dup values (4,'prakash')
insert into dup values (1,'kanna')
insert into dup values (2,'radha')

--select all rows with duplicate id's
select * from dup

--copy Only distinct datas of original table, to temp table
select distinct * into duptemp from dup

--empties the original table
delete from dup

--inserts unique records from tmep table to original table
insert into dup select * from duptemp

--task over, temp table not required
drop table duptemp

--select distinct values/ unique values/no duplicate ids from dup table
select * from dup


Regards,
Joby John
jobyjc at gmail
dot com

krishanu22

• Sep 27th, 2007

 

--Create table with duplicates
create table dup(id int, name varchar(50))
insert into dup values (1,'raja')
insert into dup values (1,'raja')
insert into dup values (2,'krishna')
insert into dup values (2,'krishna')
insert into dup values (3,'ramana')
insert into dup values (4,'prakash')
SELECT [id], [name] FROM [testdb].[dbo].[dup] order by id

--Remove duplicates
SET ROWCOUNT 1
delete dup from dup a,
(select id, count(*) as ct from dup group by id having count(*)>1) b
where a.id = b.id
WHILE @@ROWCOUNT>0
delete dup from dup a,
(select id, count(*) as ct from dup group by id having count(*)>1) b
where a.id = b.id
SET ROWCOUNT 0

--Check result
SELECT [id], [name] FROM [testdb].[dbo].[dup] order by id
drop table dup

Narendra

• Oct 6th, 2007

 

select col_name from table_name where col_name not in (select distinct col_name from table_name)

RITESH

• Oct 20th, 2007

 

The code fails if any column has null value.

sreyas kvm

 Profile Answers by sreyas kvm Questions by sreyas kvm

• Feb 3rd, 2008

 

Using DISTINCT


SELECT  DISTINCT * FROM TABLENAME

venkat.rbs

 Profile Answers by venkat.rbs 

• Feb 12th, 2008

 

if table have columns like  eno and ename(2 fields).with table name Emptab from this we can eliminate duplicate data ..with following sql statement....

select  DISTINCT (ename) from Emptab.....

prasad_4computers

 Profile Answers by prasad_4computers 

• May 31st, 2008

 

For example let us take emp table.   query to delete duplicate row  will be like this
Delete  * from  emp where Rowid   not in (select  max(Row id) from emp grop by all  columns)

ra.shinde

 Profile Answers by ra.shinde 

• Jan 5th, 2009

 

select * from abc1

--Original Table

--ID

------

--1

--2

--3

--2

--4

--5

--3

select id  into #temp from abc1 group by id having count(id) > 1

select * from #temp temp

--Duplicate Records

--Id

-----

--2

--3

--Delete Duplicate

delete from abc1 where id in 

(

select id from abc1 group by id having count(id) > 1

)

select * from abc1

--After Deleting duplicate

--ID

----

--1

--4

--5

--Insert single record for duplicate

insert into abc1

select id from #temp temp

--Final Result

select * from abc1

--Id

----

--1

--2

--3

--4

--5

drop table #temp

rahulsiras

 Profile Answers by rahulsiras 

• Feb 20th, 2009

 

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3... ;

Please check this will help

malhotrap

 Profile Answers by malhotrap 

• Jun 11th, 2009

 

There are 2 ways to achive this
1) Select Distinct * into to another table, delete all records from primary table and insert back.
But i think you dont want to do this in OLTP kind of env.

I have not yet tested but you can try this option, you want i can help you in writing code for this
Create Cursor
Check current row and match with table if another row exists
Delete where current of currsor
Fetch next of cursor

I am sure this will work...

iamrahul127

 Profile Answers by iamrahul127 

• Jul 10th, 2009

 

create table dup(id int, name varchar(50))

insert into dup values (1 ,'raja')

insert into dup values (1 ,'raja')

insert into dup values (2 ,'krishna')

insert into dup values (2 ,'krishna')

insert into dup values (3 ,'ramana')

insert into dup values (4 ,'prakash')

SELECT [id], [name] FROM [dup] order by id

----to remove duplicates

with CTE (id,idcount) as 

(SELECT id,

ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) AS idcount FROM dup

 )

DELETE

FROM CTE

WHERE idcount > 1

psingla

 Profile Answers by psingla Questions by psingla

• Mar 16th, 2010

 

SQL server 2005:
Create table test_a (a int)
insert into test_a values(1)
insert into test_a values(1)
insert into test_a values(3)
insert into test_a values(4)
insert into test_a values(5)
insert into test_a values(1)
insert into test_a values(2)
insert into test_a values(3)
insert into test_a values(4)
insert into test_a values(5)                               
WITH [test_a rowid]
AS (SELECT a.a,
Row_number()
OVER(ORDER BY a.a) num
FROM test_a a)
DELETE FROM [test_a rowid]
WHERE num NOT IN (SELECT Rank()
OVER(ORDER BY b.a)
FROM test_a b)

KhushbuAgrawal

 Profile Answers by KhushbuAgrawal 

• Dec 8th, 2010

 

We consider that we have table OURTABLE which has duplicate rows and UTABLE is the empty table in which we want to insert the non duplicate data.
  2 ways I  know

1.
insert into  UTABLE
sel
name,id
from
(sel
name,id
from OURTABLE
union
sel
name,id
from OURTABLE) as  a

Union will remove the duplicate rows.

2.
insert into UTABLE
sel
name,id
from

(sel
row_number() over(partition by name,id order by id asc) as a,
name,id
from OURTABLE
qualify a=1) b

by row_number() over(partition by name,id order by id asc) the rows will be get partitioned on name and ID and then order by id in ascending order. So if we have full row duplicate then it will get row_number as 2 and every other rows will get row_number as 1. as we want only distinct value we will consider only where row number=1 thats why we have taken qualify a=1

Wazid Ali

 Profile Answers by Wazid Ali 

• May 6th, 2011

 

we can use common table expression

with CTE_Delete
AS
(
select * ,[Duplicate] =row_number()  over (partition BY [ColumnName] Order by newID()) from  TableName
)
Delete from CTE_Delete Where Duplicate >1

You have to select all query at a time

Anupama Kanakam

 Profile Answers by Anupama Kanakam 

• May 24th, 2011

 

distinct will remove the duplicates provided the records have exact match of all column values given. If even one column value do not match, then the records still exists and they can be deleted by giving where clause.

Jainul Khan

• Jul 13th, 2011

 

How to delete duplicate records from the table

Code
SELECT DISTINCT * INTO #t2 from emp;  


--Copy your distinct data into new  temporary table like #t2


DROP TABLE emp            --delete old table like emp


SELECT * INTO emp FROM #t2   --copy again his new data to your old table like emp


SELECT *FROM emp  -- show the result

Rohit kumar patna

• Jul 31st, 2011

 

If you want to remove the duplicate value form the table you can use the primary constraints in the table.

primary key cannot be duplicate .if any key will be primary in the table you can't use use the same value in column of the table.and also you can use the unique key in the table.

Unique key is also used for reduced the redundancy from the table.but one problem is that unique key may be more than one in a table but primary key will be single in a table.

Parul

• Sep 19th, 2011

 

Code
 WITH Emp AS (SELECT ROW_NUMBER ( ) OVER (PARTITION BY Id ORDER BY Id ) AS RNUM FROM Cust ) 


 DELETE FROM Emp WHERE RNUM > 1   

Mally

• Feb 12th, 2012

 

Good Code...however the code will delete all the duplicates... you may want to add:

delete top(n) clause in your delete statement to exactly delete the required number of records.

Vijay

• May 10th, 2012

 

SELECT ID FROM TBLSAMPLE GROUP BY ID
OR
SELECT DISTINCT (ID) FROM TBLSAMPLE

Code
SELECT ID FROM TBLSAMPLE GROUP BY ID


OR


SELECT DISTINCT (ID) FROM TBLSAMPLE