1,105,344 Community Members

how to delete duplicate record in a table by using SQL query

Member Avatar
g.prabu
Newbie Poster
7 posts since Feb 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

how to delete duplicate record in a table by using SQL query

Member Avatar
pty
Posting Pro
529 posts since Oct 2005
Reputation Points: 54 [?]
Q&As Helped to Solve: 44 [?]
Skill Endorsements: 0 [?]
 
0
 

how to delete duplicate record in a table by using SQL query

duplicate of what? all duplicates? why don't you have a unique identifier? do you have any validation? why are you here? why am i answering you?

Member Avatar
g.prabu
Newbie Poster
7 posts since Feb 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

i mean if a row occurs twise we have to delete one row of that two rows. no validation. by using sql query . if u can send me that query

Member Avatar
arunendra
Newbie Poster
1 post since Apr 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

how to delete duplicate record in a table by using SQL query

I also faced the same kind of problem and solved it by myself as I never got good source.
I guess you have a table that has duplicate records but still you can identify them with a unique field, if so then follow this syntax, the theory is pretty simple, find the duplicate records with a subquery and then use the delete statement!!

for our query,I'm assuming that unique field is :U_F
the product code is " P_C
if you have multiple fields which you may have to match , then I am using another field which you may omit if you don't have and can increase the number of fields by adding them with AND.
so another field name i am using is : A_F

so the query will be:

DELETE *
FROM TableName WHERE U_F IN (select a.U_F from TableName as a, TableName AS b
WHERE a.P_C=b.P_C And a.A_F=b.A_F And a.U_F>b.U_F
and a.transaction_mode='some string/number');
'some string/number' represent the value that determines which records will be deleted!!
I hope this solves your problem!!! Mail me if you find this difficult and wanna get into more details!!:)
Bye
Arun

Member Avatar
eralper
Newbie Poster
11 posts since Aug 2005
Reputation Points: 0 [?]
Q&As Helped to Solve: 2 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,

You can find methods summarized with samples at the article named "How to delete duplicate records or rows among identical rows in a table where no primary key exists" at http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

Eralper

Member Avatar
puppinoo
Newbie Poster
1 post since May 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,
I registered just to thank you for this amazing article.
The query using the identity is simple and elegant!


Thanks a lot.
Pino

Hi,

You can find methods summarized with samples at the article named "How to delete duplicate records or rows among identical rows in a table where no primary key exists" at http://www.kodyaz.com/articles/delete-duplicate-records-rows-in-a-table.aspx

Eralper

Member Avatar
bsasiju
Newbie Poster
1 post since May 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

the simple method remove duplicate rows on the table using the following query
first find the rows which have duplicates
SELECT identity column(fid)
FROM table
GROUP BY fid
HAVING COUNT(*) > 1

and then
SET ROWCOUNT 1
DELETE FROM table
WHERE fid = 1 AND fid = 1

Member Avatar
rado
Newbie Poster
4 posts since May 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

only remove duplicate records, select duplicated fields, and then apply group by

create table #temp same schema
INSERT INTO #temp SELECT field1,field2,field{n} FROM yourtable GROUP BY field1,field2,field{n}

DELETE OLDdata
INSERT INTO yourtable select * from #temp
DROP #temp
recommend: in transaction :)

Member Avatar
Nabi_Ahmad
Newbie Poster
1 post since May 2007
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

I have a table named "delivery" that has 10 fields. From that Iwant to count most delivered items, that means I want to Count duplicates based on ItemCode and Amount and group by shop names.

The looks like

ItemCode
Amount
Shopname
zz
zz
01
100
Telebrand


01
100
Telebrand


01
100
Telebrand


02
50
Telebrand


02
50
Telebrand


when I'll run the query it should give me data like this:

ShopNmae
Totaldelivery
Telebrand
4


that means duplicates will be counted on ItemCode and Amount. And most importantly it will always 1 less of each occurance. Like 01 and 100 occured 3 times but counted 2 and 02 and 50 occured 2 times but it will count 1 and the total count will be 4. And I have a field that is delivery data, Iwant to run this count operation within a fixed period of time using between comand or anything else. Can any of you please let me know the sql code for it ?

Member Avatar
mohammeds
Newbie Poster
3 posts since Mar 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

only remove duplicate records, select duplicated fields, and then apply group by

create table #temp same schema
INSERT INTO #temp SELECT field1,field2,field{n} FROM yourtable GROUP BY field1,field2,field{n}

DELETE OLDdata
INSERT INTO yourtable select * from #temp
DROP #temp
recommend: in transaction :)

Asalamvalikum
Hi This is mohammed azhar new to comm..

i need ur help how to update multiple columns (period,section)
in which data has interchanged

reply if possible today

Question Answered as of 6 Years Ago by rado, puppinoo, bsasiju and 5 others
Member Avatar
puneetkay
Junior Poster
122 posts since Nov 2007
Reputation Points: 40 [?]
Q&As Helped to Solve: 23 [?]
Skill Endorsements: 0 [?]
 
0
 

Well guys.. im new for MS SQL.. i have a solution & it worked perfectly with me.

while creating table just add an extra attribute using identity keyword:

attributename int identity(1,1)

so everytime you insert any row, it will increase 1. so there will a unique number for every row :D

Regards!

Member Avatar
Jx_Man
Senior Poster
3,543 posts since Nov 2007
Reputation Points: 987 [?]
Q&As Helped to Solve: 542 [?]
Skill Endorsements: 74 [?]
Featured
 
0
 
DELETE FROM emp e  WHERE rowid>(SELECT MIN(ROWID) FROM emp WHERE e.empno=empno)
Member Avatar
space1000
Newbie Poster
9 posts since Mar 2008
Reputation Points: 0 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

Just solve the thing with a cursor

like
declare del_dups cursor
for
select * from <table_name>
group by column_name
having count(*) > 1

(column_name where the duplicates are)

open del_dups

fetch next from del_dups into ......

while @@fetch_status <> 0
begin
delete from table_name where current of cursor
fetch next from del_dups into ....
end

close del_dups
deallocate del_dups

For the update you should change te delete statement to

update table_name set ... = .... where current of cursor

Member Avatar
edgarkuhimbisa
Newbie Poster
1 post since Oct 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

hey im workin out smthin hre will get back to u. i seem to be havin an idea

gauravshar
Newbie Poster
1 post since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
Unverified Member
 
0
 
  1. select distinct * into newtable from oldtable
  2. truncate old table
  3. insert into oldtable select* from newtable

using this we can delete the duplicate values/records from a table

Member Avatar
msi
Newbie Poster
5 posts since Nov 2007
Reputation Points: -2 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

A few methods: sql-ex.ru

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article