Updating table but duplicate issue keeps happening

Databases
1

I have a table called “temp” (I was in the middle of teaching myself MySQL) and in this table, I have 3 columns: id, time, blah.

The table had 4 rows. I removed number 2. I then tried my hand at UPDATE via the following:

UPDATE temp SET id=2;

…only to receive an error message:

ERROR 1062 (23000): Duplicate entry ‘2’ for key ‘PRIMARY’

What gives? Obviously, I’m doing something wrong here but I had thought that since I removed the row, I could use update to bring the element back as using INSERT to do this received similar outcomes.

None of my columns are set to AUTO INCREMENT and in my mind, I can’t help but believe that I should be able to use either INSERT or UPDATE to create the element I deleted. What’s up with this?

2

Noted. :wink:

3

you did it again!!

4

I know! I was trying to be funny. :slight_smile:

I won’t do it anymore, though.

5

Ahhh! I see now. Thanks, Rudy. :slight_smile:

6

you’ve got a PRIMARY KEY defined in the table, and that’s what’s giving you the error

you said the table had 4 rows and you removed number 2, so let’s pretend that the table currently looks like this –

id   time        blah 
 1  1278986400   eenie
 3  1278990000   minie
 4  1279016460   moe

now let’s follow along and see what happens when you try the UPDATE

UPDATE temp SET id=2;

this is a perfectly valid statement, but it will fail if the table has more than one row in it

when applied to the first row (assuming for the sake of argument that it proceeds against the rows in id sequence, which isn’t actually guaranteed), it succeeds, so after it has updated the first row, the table looks like this –

id   time        blah 
 [COLOR="blue"][B]2[/B][/COLOR]  1278986400   eenie
 3  1278990000   minie
 4  1279016460   moe

as the UPDATE statement proceeds through the table (and it’s going to try to update all the rows, because the UPDATE statement does not have a WHERE clause to limit the scope of what will be updated), here’s what happens when it updates the next row –

id   time        blah 
 [COLOR="Blue"][B]2[/B][/COLOR]  1278986400   eenie
 [COLOR="red"][B]2[/B][/COLOR]  1278990000   minie
 4  1279016460   moe

at this point, the statement fails, because the PRIMARY KEY will not allow more than one instance of each value of the id – primary keys are unique, by definition

you can’t have more than one 2, so that’s why you get the error message

7

Would be handy indeed if we see the table structure. That makes things more visual. There must be some of auto_increment be there.

The show create table shows you the structure of the table how you made it in the first place. In your case SHOW CREATE TABLE temp;

and one last question, how do you try to replace the record from a cms or in a mySql administrator?

8

not quite

run this query please –

SHOW CREATE TABLE temp

please extract everything it produces and paste it here

9

Sorry, Rudy, but I’m not sure I follow…

What do you mean “SHOW CREATE TABLE?” Are you talking about the structure (i.e.- Field, Type, etc…)?" If so, see below:

Fields: id, time, blah
Type: int(11), int(11), varchar(32)
Null: No, No, Yes
Key: id is Primary Key

Does this help?

10

noted :cool:

11

you’re welcome

and in general, it is not necessary to quote the entire message you’re replying to

:slight_smile:

12

Interesting command there. I never knew about this one! :slight_smile:

As for the structure, here is what was displayed:

temp | CREATE TABLE temp (
id int(11) NOT NULL,
timeint(11) NOT NULL,
blah varchar(32) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

donboe, currently, I’m using the WAMP MySQL console to practice these commands on. I’m not using a CMS or anything like that at the moment (assuming you were directing that to me about the CMS stuff.) Coincidentally, I’m studying a book I won on here at SitePoint.com called “Simply SQL” by some guy who isn’t that good at it and pretends to have around 20 years of experience (ha). :wink: :goof:

Seriously, it’s a great book and I’ve enjoyed it so far but this issue I’m up against at the moment is really twisting my brain around… What gives?

13

we can get to the bottom of things rather more easily if you would please do a SHOW CREATE TABLE for the table