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

Please support our MS SQL advertiser: Intel Parallel Studio Home
Thread Solved

Join Date: Feb 2007
Posts: 7
Reputation: g.prabu is an unknown quantity at this point 
Solved Threads: 0
g.prabu g.prabu is offline Offline
Newbie Poster

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

 
0
  #1
Feb 19th, 2007
how to delete duplicate record in a table by using SQL query
Reply With Quote Quick reply to this message  
Join Date: Oct 2005
Posts: 521
Reputation: pty is on a distinguished road 
Solved Threads: 37
pty's Avatar
pty pty is offline Offline
Posting Pro

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

 
0
  #2
Feb 19th, 2007
Originally Posted by g.prabu View Post
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?
Note to self... pocket cup
Reply With Quote Quick reply to this message  
Join Date: Feb 2007
Posts: 7
Reputation: g.prabu is an unknown quantity at this point 
Solved Threads: 0
g.prabu g.prabu is offline Offline
Newbie Poster

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

 
0
  #3
Feb 20th, 2007
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
Reply With Quote Quick reply to this message  
Join Date: Apr 2007
Posts: 1
Reputation: arunendra is an unknown quantity at this point 
Solved Threads: 1
arunendra arunendra is offline Offline
Newbie Poster

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

 
0
  #4
Apr 9th, 2007
Originally Posted by g.prabu View Post
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
Reply With Quote Quick reply to this message  
Join Date: Aug 2005
Posts: 6
Reputation: eralper is an unknown quantity at this point 
Solved Threads: 2
eralper eralper is offline Offline
Newbie Poster

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

 
0
  #5
Apr 16th, 2007
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/delet...n-a-table.aspx

Eralper
Reply With Quote Quick reply to this message  
Join Date: May 2007
Posts: 1
Reputation: puppinoo is an unknown quantity at this point 
Solved Threads: 1
puppinoo puppinoo is offline Offline
Newbie Poster

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

 
0
  #6
May 18th, 2007
Hi,
I registered just to thank you for this amazing article.
The query using the identity is simple and elegant!


Thanks a lot.
Pino

Originally Posted by eralper View Post
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/delet...n-a-table.aspx

Eralper
Reply With Quote Quick reply to this message  
Join Date: May 2007
Posts: 1
Reputation: bsasiju is an unknown quantity at this point 
Solved Threads: 1
bsasiju bsasiju is offline Offline
Newbie Poster

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

 
0
  #7
May 19th, 2007
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
Reply With Quote Quick reply to this message  
Join Date: May 2007
Posts: 4
Reputation: rado is an unknown quantity at this point 
Solved Threads: 1
rado rado is offline Offline
Newbie Poster

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

 
0
  #8
May 21st, 2007
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
Reply With Quote Quick reply to this message  
Join Date: May 2007
Posts: 1
Reputation: Nabi_Ahmad is an unknown quantity at this point 
Solved Threads: 1
Nabi_Ahmad Nabi_Ahmad is offline Offline
Newbie Poster

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

 
0
  #9
May 23rd, 2007
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 ?
Reply With Quote Quick reply to this message  
Join Date: Mar 2008
Posts: 3
Reputation: mohammeds is an unknown quantity at this point 
Solved Threads: 1
mohammeds mohammeds is offline Offline
Newbie Poster

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

 
0
  #10
Mar 5th, 2008
Originally Posted by rado View Post
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
Reply With Quote Quick reply to this message  
Reply

This thread has been marked solved.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC