7 {{ upvoteCount | shortNum }}
7 {{ upvoteCount | shortNum }}

#1062 - Duplicate entry '23933-27243-0' for key 'membersid'Hi guys, I have a table, the table name

by basketmen
6 replies
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

##1062 #duplicate #entry #guys #key #membersidhi #table
  • Profile picture of the author john_kennedy
    Can you provide a screenshot of how you have membersid setup?

    John
    {{ DiscussionBoard.errors[5088706].message }}
    • Profile picture of the author basketmen
      Originally Posted by john_kennedy View Post

      Can you provide a screenshot of how you have membersid setup?

      John
      its same like this categoryid structure screenshot, only in the Column name is membersid




      {{ DiscussionBoard.errors[5088753].message }}
  • Profile picture of the author john_kennedy
    On the membersid table have you tried checking the Auto_Increment box?
    {{ DiscussionBoard.errors[5088797].message }}
  • Profile picture of the author KirkMcD
    What are the indexes on the table?
    Also "replace" is a string function, so what are you trying to do? Set category everyone in cat 1 to cat 2?
    Replace would change every instance of 1 to a 2. So a cat 11 would be changed to 22.
    If you just want to change cat 1 to 2, add a where clause.
    {{ DiscussionBoard.errors[5094000].message }}
  • Profile picture of the author SteveJohnson
    Did someone also tell you to add the membersid as a primary key?

    If your data setup requires that members be able to be in multiple categories, you'll need to remove the primary key from that column, then do your replace as Kirk outlined above.

    And whatever you do, do NOT check the auto-increment box.

    You DO have a backup of your original database, don't you?
    Signature

    The 2nd Amendment, 1789 - The Original Homeland Security.

    Gun control means never having to say, "I missed you."

    {{ DiscussionBoard.errors[5094478].message }}
  • Profile picture of the author Iain Key
    It sounds like a key constraint, memberid is either set as the primary key or is a foreign key with the unique constraint set and therefore can not be repeated

    Also read what Kirk said above about the REPLACE state
    here's a link about REPLACE in more depth, Using REPLACE in an UPDATE statement - SQLTeam.com
    I don't think it's what you really mean.

    I suspect what you really want is something like;
    update `account` set categoryid = 2 where categoryid = 1

    but let us know how you get on
    {{ DiscussionBoard.errors[5113240].message }}

Trending Topics