CODEWITHSUNDEEP
phpmysql
andy222
andy222

Reputation: 125

Duplicate entry mysql unique field

I have a 'code_number' column with varchar(25) field type, and I make it into Unique field type. I try to insert 2 number to the number column with different number. First number is '112225577' and for the second number is '112228899'. Now I'm trying to update the first number, and only change 3 last digit number '577' with '433', became '112225433'. But I got error Duplicate entry '112225433' for key 'code_number'.

How can it be duplicate? I only have 2 data and the data is not same. Can anybody explain to me why this happening?

UPDATE

here is my code.

Product

id INT(11)
product VARCHAR(250)
code_number VARCHAR(25) UNIQUE
...

Account

id INT(11)
name VARCHAR(250)
email VARCHAR(100) UNIQUE
...

And my query is like this:

$this->db->set('code_number','112225433');
$this->db->where('code_number','112225577');
$this->db->update('product');

Same problem goes to email column when i try to update account record. here is the code sample:

$this->db->set('email','[email protected]');
$this->db->where('name','Andy');
$this->db->update('account');

the email data in email column where name='Andy' is '[email protected]'.

Upvotes: 0

Views: 58

Answers (2)

JLavoie
JLavoie

Reputation: 17596

It could be a problem with your SQL editor. For instance, it could show you the previous value but it could have update it already, so that you you believe that it has not been changed yet. I ever had such problem like this before: the value was updated in the DB but the editor had no updated yet.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can definitely do this without a problem (see the SQL Fiddle here).

I imagine that you are doing something where both rows are getting updated to the same value, the equivalent of:

update t
    set code_number = '112225433';

This will generate exactly the error you report. There are, no doubt, many SQL queries that would have this effect. But, this would generate such an error.

Upvotes: 1

Related Questions