On 19 July 2010 15:01, Richard Quadling <rquadling@xxxxxxxxx> wrote: > On 19 July 2010 05:44, Peter <peters@xxxxxxxxxxxx> wrote: >> Hi All, >> >> I have a table which contain's some duplicate rows. I just want to delete >> the duplicate records alone >> not original records. >> >> Assume my table as look as below >> >> column1 column2 >> 1 >> a >> 1 >> a >> 2 >> b >> 3 >> c >> 3 >> c >> >> >> >> i want the above table need to be as below, After executing the mysql >> query. >> >> column1 >> column2 >> 1 >> a >> 2 >> b >> 3 >> c >> >> >> >> >> Thanks in advance.. >> Slightly more concise ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Get the earliest UniqueIDs for each Col1, Col2 pairing. SELECT MIN(UniqueID) FROM Table GROUP BY Col1, Col2 ) http://www.devx.com/tips/Tip/14665 DELETE Table FROM Table T1, Table T2 WHERE T1.Col1 = T2.Col1 AND T1.Col2 = T2.Col2 AND T1.UniqueID > T2.UniqueID http://www.cryer.co.uk/brian/sql/sql_delete_duplicates.htm etc. Many different ways. http://www.orafaq.com/faq/how_does_one_eliminate_duplicates_rows_from_a_table Method 3 should be the fastest. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
