Re: [PHP] MySQL Query Puzzle
I am very keen to see a closure to this thread so that I can add to my snippets. Let's all know what worked best out of many solutions that have been proposed. --Shreyas On Tue, Jul 20, 2010 at 10:07 AM, Jim Lucas wrote: > Peter 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.. >> >> Regards >> Peter >> >> > Use the SQL command alter with the ignore flag. > > ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` ) > > I tested this on my test DB and it worked fine. It erased all the > duplicates and left one instance of the multiple entry values. > > This will add a permanent unique restraint to the table. So, you will > never have dupps again. > > Jim Lucas > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
Peter 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.. Regards Peter Use the SQL command alter with the ignore flag. ALTER IGNORE TABLE `your_table` ADD UNIQUE ( `column1` , `column2` ) I tested this on my test DB and it worked fine. It erased all the duplicates and left one instance of the multiple entry values. This will add a permanent unique restraint to the table. So, you will never have dupps again. Jim Lucas -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Just to add more perspective : You will have a table with DISTINCT values. Drop the initial table (better take a back-up); copy from the temporary table which will have only DISTINCT values. Regards, Shreyas On Mon, Jul 19, 2010 at 7:58 PM, Shreyas Agasthya wrote: > How about this : > > CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); > INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; > > Regards, > Shreyas > > On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling wrote: > >> On 19 July 2010 05:44, Peter 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.. >> > >> > Regards >> > Peter >> > >> >> If your table had a db generated sequential unique identifier (an >> identity / autoinc), then something along these lines may be what you >> are looking for ... >> >> -- Delete everything except the UniqueIDs we want to keep. >> DELETE FROM >>Table >> WHERE >>UniqueID NOT IN >>( >>-- Just get the UniqueIDs we want to keep. >>SELECT >>UniqueID >>FROM >>( >>-- Get the earlist UniqueID for each Col1, Col2, >> pairing. >>SELECT >>Col1, >>Col2, >>MIN(UniqueID) AS UniqueID >>FROM >>Table >>GROUP BY >>Col1, >>Col2 >>) >>) >> >> UNTESTED >> >> -- >> PHP General Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > > > -- > Regards, > Shreyas Agasthya > -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
How about this : CREATE TEMPORARY TABLE bad_temp1 (id INT,name VARCHAR(20)); INSERT INTO bad_temp1 (id,name) SELECT DISTINCT id,name FROM SAMPLE; Regards, Shreyas On Mon, Jul 19, 2010 at 7:31 PM, Richard Quadling wrote: > On 19 July 2010 05:44, Peter 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.. > > > > Regards > > Peter > > > > If your table had a db generated sequential unique identifier (an > identity / autoinc), then something along these lines may be what you > are looking for ... > > -- Delete everything except the UniqueIDs we want to keep. > DELETE FROM >Table > WHERE >UniqueID NOT IN >( >-- Just get the UniqueIDs we want to keep. >SELECT >UniqueID >FROM >( >-- Get the earlist UniqueID for each Col1, Col2, > pairing. >SELECT >Col1, >Col2, >MIN(UniqueID) AS UniqueID >FROM >Table >GROUP BY >Col1, >Col2 >) >) > > UNTESTED > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- Regards, Shreyas Agasthya
Re: [PHP] MySQL Query Puzzle
On 19 July 2010 15:01, Richard Quadling wrote: > On 19 July 2010 05:44, Peter 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
Re: [PHP] MySQL Query Puzzle
On 19 July 2010 05:44, Peter 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.. > > Regards > Peter > If your table had a db generated sequential unique identifier (an identity / autoinc), then something along these lines may be what you are looking for ... -- Delete everything except the UniqueIDs we want to keep. DELETE FROM Table WHERE UniqueID NOT IN ( -- Just get the UniqueIDs we want to keep. SELECT UniqueID FROM ( -- Get the earlist UniqueID for each Col1, Col2, pairing. SELECT Col1, Col2, MIN(UniqueID) AS UniqueID FROM Table GROUP BY Col1, Col2 ) ) UNTESTED -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Hi Shiplu, Thanks for reply. Distinct function hide the duplicate records while we selecting the record through the Query i want to remove the duplicate entries in my table i need a Delete Query instead of Select Query shiplu wrote: Use distinct. SELECT DISTINCT COLUMN1, COLUMN2 FROM ... ... Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
Use distinct. SELECT DISTINCT COLUMN1, COLUMN2 FROM ... ... Shiplu Mokadd.im My talks, http://talk.cmyweb.net Follow me, http://twitter.com/shiplu SUST Programmers, http://groups.google.com/group/p2psust Innovation distinguishes bet ... ... (ask Steve Jobs the rest) -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On Mon, Jul 19, 2010 at 10:44 AM, Peter 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.. > > Regards > Peter > Create a table with similar structure and add UNIQUE INDEX on both columns. Execute the following query: INSERT IGNORE INTO NEW_TABLE (column1, column2) SELECT column1, column2 FROM OLD_TABLE This will give you distinct rows as required. -- Keep Smiling :-) Shafiq Rehman Blog: http://shafiq.pk, Twitter: http://twitter.com/shafiq -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] MySQL Query Puzzle
On Mon, Jul 19, 2010 at 10:14:30AM +0530, Peter 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 > > If you're looking for a MySQL solution to this, this is the wrong list to ask the question on. In fact, I'd be surprised to find a MySQL query which would do this. For a PHP solution, you'll need to query MySQL for all the rows, in order by the column you want to use to kill duplicates. Then loop through the rows one at a time in PHP, checking the contents of that column against the last iteration. If they are the same, issue a DELETE command in MySQL. Continue the loop until all rows are exhausted. Paul -- Paul M. Foster -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
