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?

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

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

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

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


Thanks a lot.
Pino

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

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

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 ?

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