CODEWITHSUNDEEP
phpmysql
Dan
Dan

Reputation: 29

Deleting Duplicates but keep the earliest date

Somehow my database table duplicated a bunch of values based on a field called kdaccount.

What I'm trying to do is delete all duplicate kdaccounts with the exception of one, and I want that one to be the earliest submitted date.

I wrote a query to identify the duplicate accounts, but I'm now sure how to structure the mysql query to delete them.

$getVendors = mysql_query("SELECT vendor, nameid, kdaccount, MIN(dw_vendors.submitteddate) as `smallestDate` 
                                                FROM pr_po_main 
                                                LEFT JOIN dw_vendors ON pr_po_main.vendor = dw_vendors.kdaccount 
                                                WHERE dw_vendors.submitteddate <> '' GROUP BY vendor HAVING COUNT(dw_vendors.kdaccount) > '1'") or die("Get Vendors: " . mysql_error());

Upvotes: 0

Views: 123

Answers (2)

Simon Rapilly
Simon Rapilly

Reputation: 393

Here posted an exemple on how to achieve that on Sql Fiddle

Linky link

That should show you the principle of the thing

Just in case here is the code 

DELETE Test 
FROM Test 
LEFT OUTER JOIN (SELECT MIN(comp) AS compMin, groupval
                FROM Test
                GROUP BY groupval) AS Test2 
ON Test.groupval = Test2.groupval AND Test.comp <> Test2.compmin
WHERE Test2.groupval IS NOT NULL

Table def is has follow 

CREATE TABLE Test 
    (
        comp int, 
        groupval int 
    );

Edit:That should work without the having clause btw

Upvotes: 0

Ayyappan Sekar
Ayyappan Sekar

Reputation: 11475

Hope the following solution will help you,

1.create a duplicate table of dw_vendors

CREATE TABLE dw_vendors_dup LIKE dw_vendors

2.make the kdaccount as unique

ALTER TABLE dw_vendors_dup ADD UNIQUE (kdaccount);

3.insert the existing values to the new table ignoring the duplicates

INSERT IGNORE INTO dw_vendors_dup (vendor, nameid, kdaccount, submitteddate) 
SELECT vendor, nameid, kdaccount, submitteddate FROM dw_vendors
ORDER BY submitteddate

4.delete the old table dw_vendors and rename the duplicate one to dw_vendors

Upvotes: 1

Related Questions