Hi,
I think the query has to be considering the count rather than the sum.
the query can be like this:
select pkfield1[,pkfield2[,pkfield3[,....]]], count(1) from <your_table>
group by pkfield1[,pkfield2[,pkfield3[,....]]]
having count(1) > 1
Here u can add n-number of feilds which u want make PK.
Reg,
Eldo.
On Fri, 08 Oct 2004 09:37:11 +1000, Daniel Kasak
<[EMAIL PROTECTED]> wrote:
> gerald_clark wrote:
>
> > Batara Kesuma wrote:
> >
> >> 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.
> >>
> >>
> > try ALTER IGNORE TABLE.
>
>
> 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]
>
>
--
Thanks & Regards,
Eldo Skaria
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
