dealing with duplicates in database 1

[compudude86]

ok, so i have a table of products, called "products" located in a database called "Book". my problem is when i update products, using a csv file through phpmyadmin, it will add another row, all the same info (price, description, etc) but different dates, and different primary keys (auto-increment). is there a query i can run on this table that would go through it, find a duplicate, and delete the one with the older date?

 

[r937]

the fact that your primary key is an auto_increment means that you will need an alternate key to determine duplicates

from your description, the only way to tell if it's a duplicate would be if it has "the same info (price, description, etc)"

delete P
  from products as P
inner 
  join (
       select price
            , description
            , etc
            , min(date) as min_date
         from products
       group 
           by price
            , description
            , etc
       having count(*) > 1
       ) as M
    on M.price       = P.price      
   and M.description = P.description
   and M.etc         = P.etc        
   and M.min_date   <> P.date

r937.com | rudy.ca

 

[compudude86]

thank you very much, it worked, it took out some 634 duplicate records.

 

[compudude86]

ok, ive modified it to this and gotten it to work:

<code> delete P
from Products as P
inner
join (
select Description
, NetBtl
, min(Date) as min_date
from Products
group
by Description
, NetBtl
having count(*) > 1
) as M
on M.Description = P.Description
and M.NetBtl = P.NetBtl
and M.min_Date <> P.Date

</code>

and that deletes the duplicates. now, i have a field called "Description", thats the description of the item. i then have price fields, "List, Discount, Net, NetBtl", and the date, "Date". now, i want to make the query look at the item, if the description is the same description, different price, and different date, i want it to take the higher date, but i also want it to take out duplicates showing identical entries like the first query did.