MySQL Query Puzzle
in [General]
|
From: Peter on 19 Jul 2010 00:44 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
From: Paul M Foster on 19 Jul 2010 01:07 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
From: Shafiq Rehman on 19 Jul 2010 01:23 On Mon, Jul 19, 2010 at 10:44 AM, Peter <peters(a)egrabber.com> 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
From: shiplu on 19 Jul 2010 07:24 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)
From: Peter on 19 Jul 2010 09:15
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) > > |