Need explanation for Cannot insert duplicate key row in object error

Question

I’m doing:

SELECT uid, intStatus from dbo.MYTABLE 
WHERE 
intStatus = 10 and uid = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'

And I get:

        uid                            |  intStatus 
-------------------------------------- +---------------
'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1  | 10

Then I need to do an update, I need the intStatus to be 18 for that uid. So I do:

UPDATE dbo.MYTABLE  SET intStatus = 18 
WHERE 
intStatus = 10 and uid = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'

And here’s where I’m getting the error:

Cannot insert duplicate key row in object 'dbo.MYTABLE' with unique index 'ixMYTABLE'.

Can anyone, please, tell me why I’m getting this error? How can I solve it?

uid is primary key for MYTABLE

intStatus is a not null int

Thanks a ton!

Solution

Probably you have already a row with intStatus 18 and uid 'a1a1...'

Try to run and see if you have already the row that you try to create with your UPDATE:

SELECT uid, 
       intStatus 
  FROM dbo.MYTABLE 
 WHERE intStatus = 18 
   AND uid = 'a1a1a1a1-a1a1-a1a1-a1a1-a1a1a1a1a1a1'