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:
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!!:)
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
when I'll run the query it should give me data like this:
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 ?