Different Duplicate Issue

[tgavin]

I wasn't thinking when designing the database and forgot to apply UNIQUE to a couple fields (field1, field2). Of course, after a couple thousand records it occurs to me and I want to fix it. However, trying to add UNIQUE at this point gives me the old #1062 duplicate entry error.

The problem is, that some of the records have no value in these fields, and some do, so this query isn't doing the trick.

SELECT field1, COUNT(*)
FROM table
GROUP BY field1
HAVING COUNT(*) > 1

How do I find the records that have the duplicate(s)?

[r937]

How do I find the records that have the duplicate(s)?

SELECT *
  FROM daTable
 WHERE field1 IN
       ( SELECT field1
           FROM daTable
         GROUP 
             BY field1
         HAVING COUNT(*) > 1 )

[tgavin]

Thank you. However, that showed all of the records with the empty field. Just the opposite of what I'm looking for.

[r937]

sorry, i thought it would show all the rows with duplicates

are the empty fields actually empty, or are they NULL?

so you're interested in the non-empty and/or non-NULL duplicates only?

[tgavin]

are the empty fields actually empty, or are they NULL?

The row is NOT NULL, and nothing was inserted, so they should be empty.

so you're interested in the non-empty and/or non-NULL duplicates only?

Yes.

[r937]

SELECT *
  FROM daTable
 WHERE field1 IN
       ( SELECT field1
           FROM daTable
        WHERE field1 <> ''
         GROUP 
             BY field1
         HAVING COUNT(*) > 1 )

[tgavin]

Wonderful! So, I ran that on both fields and had zero results. I'm assuming that there are no duplicates. Correct? If that's the case, why can't I convert those fields to UNIQUE without receiving an duplicate entry error?

[r937]

make a copy of the table

on the copy, use ALTER IGNORE TABLE ADD UNIQUE

this will remove the duplicates

now compare the copy to your original

this will highlight all the rows that were removed because of duplicates

[tgavin]

ALTER IGNORE TABLE copy ADD UNIQUE KEY (field1)

removed 2 rows. What's the best way to compare the two tables to find out which ones?

[r937]

SELECT live.*
  FROM live
LEFT OUTER
  JOIN copy
    ON copy.pkey = live.pkey
 WHERE copy.pkey IS NULL

[tgavin]

SELECT live.*
FROM live
LEFT OUTER
JOIN copy
ON copy.uid = live.uid
WHERE copy.uid IS NULL

That was kind of the reverse of what I was looking for. It showed me all of the users where field1 is empty. I tried IS NOT NULL and received 304 users. Not the 2 that were omitted.


Tried this too...
SELECT a.uid
FROM live a
INNER JOIN copy b ON a.uid = b.uid
WHERE a.field1 <> b.field1

[r937]

the sql i gave you is bulletproof -- you just have to use the right columns on the join

what are the real names of your table's primary key, and the columns that you wanted the UNIQUE applied to?

[r937]

FYI in mysql you can use the first three of these 5 ways -- http://sqlblog.com/blogs/denis_gobo/...2/20/5169.aspx

the OUTER JOIN is the one i suggested

[tgavin]

Here's something 'funny'. I exported the fields to a text file and then opened it in BBEdit. I ran the 'process duplicate lines' filter on the data and came up with nothing. Do I even have duplicates? It doesn't look that way now, but I'm not sure because mysql won't let me change the fields to UNIQUE.

Here's the table

CREATE TABLE IF NOT EXISTS `users` (
`uid` bigint(20) unsigned NOT NULL auto_increment,
`token` varchar(32) collate utf8_unicode_ci NOT NULL default '',
`key` varchar(32) collate utf8_unicode_ci NOT NULL default '',
PRIMARY KEY (`uid`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2008 ;

The fields 'token' and 'key' are the two that I want to make UNIQUE.

[r937]

KEY for sure is a reserved word, TOKEN probably isn't

if you had mentioned the real column names at the beginning, instead of using the rather lame -- no offence -- "field1", then we could have saved ourselves a lot of time

did you want separate UNIQUE keys on `token` and `key`, or a composite?

[tgavin]

Sorry! In my haste to get you the table name I did a find/replace in usr_ and it removed those from the field names! I need to slow down...

The correct field names are
uid
usr_token
usr_key

usr_token and usr_key both need to be UNIQUE.

[r937]

usr_token and usr_key both need to be UNIQUE.

individually or together as a composite key?

[tgavin]

Individually please. I have no idea what a composite key is.

[r937]

could you please do an actual SHOW CREATE TABLE for me, thanks

[tgavin]

CREATE TABLE IF NOT EXISTS `users` (
`uid` bigint(20) unsigned NOT NULL auto_increment,
`usr_fname` varchar(30) collate utf8_unicode_ci NOT NULL default '',
`usr_lname` varchar(30) collate utf8_unicode_ci NOT NULL default '',
`usr_email` varchar(60) collate utf8_unicode_ci NOT NULL default '',
`usr_uname` varchar(60) collate utf8_unicode_ci NOT NULL default '',
`usr_passwd` varchar(32) collate utf8_unicode_ci NOT NULL default '',
`usr_website` varchar(255) collate utf8_unicode_ci NOT NULL default '',
`usr_ip_address` varchar(60) collate utf8_unicode_ci NOT NULL default '',
`usr_token` varchar(32) collate utf8_unicode_ci NOT NULL default '',
`usr_key` varchar(32) collate utf8_unicode_ci NOT NULL default '',
`usr_verified` char(1) collate utf8_unicode_ci NOT NULL default '0',
`usr_access_level` char(1) collate utf8_unicode_ci NOT NULL default 'u',
`usr_date_added` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (`uid`),
UNIQUE KEY `usr_uname` (`usr_uname`),
UNIQUE KEY `usr_email` (`usr_email`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2008 ;

[guelphdad]

a composite key is where the key spans more than one column.
if you had separate unique keys on firstname and lastname columns you could only have one Ken or one Lake but if the unique key was on both columns you could have Ken Brown and Ken Lake for instance, which wouldn't be allowed if the unique key was on firstname alone.

[r937]

aha, it seems you have no NULLs, but might have a lot of "empty" fields

if there is more than one, UNIQUE will not work

however, if they were NULL, then you could have as many NULLs in the UNIQUE column as you wish


what was it you were trying to do originally again?

[tgavin]

what was it you were trying to do originally again?

When designing the users table, I failed to make usr_token and usr_key UNIQUE. I now want to do that. However, I have over 2,000 rows in the users table. When trying to convert the fields to UNIQUE I receive the mysql #1062 duplicate entry error.

I want to make usr_token and usr_key UNIQUE without destroying records.

[tgavin]

a composite key is where the key spans more than one column.
if you had separate unique keys on firstname and lastname columns you could only have one Ken or one Lake but if the unique key was on both columns you could have Ken Brown and Ken Lake for instance, which wouldn't be allowed if the unique key was on firstname alone.

Thank you.

[r937]

I want to make usr_token and usr_key UNIQUE without destroying records.

this will be somewhat of a problem -- what if two rows have the same value? one of them will need to change, or, alternatively, to be deleted

you gots to destroy something

please tell me what you get for this query --

SELECT usr_token
     , COUNT(*) AS dupes
  FROM users
GROUP
    BY usr_token
HAVING COUNT(*) > 1
ORDER
    BY dupes DESC LIMIT 1