gerald_clark wrote:
Batara Kesuma wrote:
try ALTER IGNORE TABLE.Hi, I have a table that looks like: CREATE TABLE `message_inbox` ( `member_id` mediumint(8) unsigned NOT NULL default '0', `message_id` int(10) unsigned NOT NULL default '0', `new` enum('y','n','replied') NOT NULL default 'y', `datetime` datetime default NULL, KEY `idx_1` (`member_id`,`new`), KEY `idx_2` (`member_id`,`datetime`) ) TYPE=InnoDB
Now, I want to add a primary key to it. ALTER TABLE message_inbox ADD PRIMARY KEY (member_id, message_id)
But there are already some duplicated entries. ERROR 1062: Duplicate entry '10244-80871' for key 1
How can I tell MySQL to delete the duplicated entry and continue to make primary key? Is there any efficient way to do this? Thank you very much.
That hardly seems like a solution. If the above works, then I'd call that 'feature' a bug.
You need to remove the duplicates from your table before creating a primary key, otherwise what are you creating the key for in the first place?
Create a query that finds the duplicates. Choose the ones you want to delete, and delete them manually.
In your particular case, as you're trying to put a key across 2 columns, you really do have a problem. I'd *usually* suggest something like:
select sum(1) as number_of_duplicates, my_key_field from my_table group by my_key_field having sum(1)>1
However this won't work if your key is going to go across more than one field. I suppose you could concat() the fields together. It's not exactly the perfect solution, but it sounds like you don't exactly have perfect data to start with, and since you're doing this manually, you can deal with it. Try something like:
select sum(1) as number_of_duplicates, concat(member_id, '___', message_id) as my_problem
from message_inbox
group by concat(member_id, '___', message_id)
having sum(1)>1
Have fun.
Dan
-- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
