Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: Display duplicate entries

    I was trying to make the column col1 UNIQUE but phpMyAdmin gave #1062 - Duplicate entry 'text1' for key 2
    Code:
    ALTER TABLE `tbl1` ADD UNIQUE (`col1`) ;
    There seems to be many duplicate entries which wont make my ALTER TABLE work - I want to view all entries that have a duplicated col1 - how do I for this query ?

    Thanks

  2. #2
    Join Date
    May 2005
    Posts
    13
    Hi,
    You Can Remove Your duplicate enteries by the following Sql Query

    Create Table TableName1 as Select distinct(filedname) from TableName

    For Example

    Create Table samplenew as Select distinct(sl_no) from Sample;

    Then you can use the samplenew for your alter statement (or) you can rename the samplenew to sample by deleting the sample table.

    I hope i would be helpfull to you

    Regards

    Balaji

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    I wanted to display the duplicate values - anyway I got a workaround using HAVING. But I wanted the id also to be displayed alongside each duplicate entry.
    The following query would return 50 rows - say there are 2 duplicates for each row (COUNT(`col1`) being 2 for each row) - but I was hopin for a solution to return 100 rows, id, col1.
    Code:
    SELECT `col1`, COUNT(`col1`)
    FROM `tbl1`
    GROUP BY `col1`
    HAVING COUNT(`col1`) > 1
    Thanks anyway,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    display the rows that have dupe col1 values:
    Code:
    select id, col1
      from tbl1
     where col1
        in (
           SELECT col1
             FROM tbl1
           GROUP 
               BY col1
           HAVING COUNT(*) > 1
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Thanks very much - this did solve my problem - this query on my laptop took 8 min to execute though ! Is there any way to optimize it for speed ? The table has 6000 records. Im sure my linux server host would be faster, but the number of records will grow over time.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create an index on col1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    My initial reason for all this was because I wanted to make col1 UNIQUE. Now that I got all duplicates and replaced them with different values, I did
    ALTER TABLE `tbl1` ADD UNIQUE (`col1`) ;
    But this is after I changed duplicate entries, before that on what basis can I create an index ?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ALTER TABLE tbl1 ADD INDEX (col1) ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Im assuming this statement given above was to be done before my correcting duplicates and making col1 unique.
    But creating an index with no size would make no difference would it ?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "with no size" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •