Display duplicate entries

anjanesh · #1 (**permalink**) 03-02-06, 06:57

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

doubtsdb · #2 (**permalink**) 03-02-06, 07:23

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

anjanesh · #3 (**permalink**) 03-02-06, 07:57

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,

r937 · #4 (**permalink**) 03-02-06, 19:51

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
       )

anjanesh · #5 (**permalink**) 03-03-06, 00:07

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.

r937 · #6 (**permalink**) 03-03-06, 04:57

create an index on col1

anjanesh · #7 (**permalink**) 03-03-06, 09:31

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 ?

r937 · #8 (**permalink**) 03-03-06, 10:13

ALTER TABLE tbl1 ADD INDEX (col1) ;

anjanesh · #9 (**permalink**) 03-03-06, 11:29

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 ?

r937 · #10 (**permalink**) 03-03-06, 12:03

what does "with no size" mean?

Internet Services

Web Development

Digital Marketing

Consumer Tech