Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : #1062 - Duplicate entry '23933-27243-0' for key 'membersid'

[basketmen]

Hi guys,

I have a table, the table name is account,
its have 2 fields : membersid, and categoryid



i want to change the categoryid field content, i run this in phpmyadmin :

update `account` set categoryid = replace (categoryid, '1' , '2');

but get this error message

#1062 - Duplicate entry '23933-27243-0' for key 'membersid'

- what should i do so i can change the field content? Please help.
I already tried changing the field TYPE, from INT, to BIGINT, like other person suggested, but still get that error message

- this is the screenshot from the field Structure

http://i291.photobucket.com/albums/l...tmen/field.jpg

[MrKenTX]

Have you checked to see if there are more than one record in the account table with membersid of '23933-27243-0'?

[foobic]

Originally Posted by MrKenTX

Have you checked to see if there are more than one record in the account table with membersid of '23933-27243-0'?

Indeed, that does seem to be what the error message is telling you.

I'd also suggest extreme caution with queries like this:

Originally Posted by basketmen

update `account` set categoryid = replace (categoryid, '1' , '2');

That's using the data in categoryid (presumably integer) as a string, and replacing instances of 1 with 2. So 1 becomes 2, 100 becomes 200, 311 become 322 etc. What you probably want here is:

UPDATE `account` set `categoryid` = 2 WHERE `categoryid` = 1;

[Grumps]

It sounds to me like you have a multi-row unique index set. And changing this will violate that unique index.

Check what keys you have setup.
Then, really think to yourself, is this change something you REALLY want to do? Are you sure you aren't doing something that'll break your program? Otherwise, you wouldn't have made such a unique index.
If yes, then just change the unique index to just index. This will resolve your duplicate key problem.