title: SQL/DB2 SQLSTATE=23505 error when executing an UPDATE statement
body: <p>I am getting a SQLSTATE=23505 error when I execute the following DB2 statement:</p>
  
  <pre><code>update SEOURLKEYWORD 
  set URLKEYWORD = REPLACE(URLKEYWORD, '/', '-') 
  where STOREENT_ID = 10701 
  and URLKEYWORD like '%/%';
  </code></pre>
  
  <p>After a quick search, a SQL state 23505 error is defined as follows:</p>
  
  <blockquote>
    <p>AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS RID OF EXISTING ROW IS X</p>
  </blockquote>
  
  <p>The full error I am seeing is:</p>
  
  <blockquote>
    <p>The full error I am seeing is:</p>
    
    <p>DB2 Database Error: ERROR [23505] [IBM][DB2/LINUXX8664] SQL0803N  One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "2" constrains table "WSCOMUSR.SEOURLKEYWORD" from having duplicate values for the index key.  SQLSTATE=23505
        1   0</p>
  </blockquote>
  
  <p>I'm not sure what the "index identified by '2'" means, but it could be significant.</p>
  
  <p>The properties of the columns for the SEOURLKEYWORD table are as follows:</p>
  
  <p><a href="https://i.stack.imgur.com/1bMhc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1bMhc.png" alt="enter image description here"></a></p>
  
  <p>Based on my understanding of this information, the only column that is forced to be unique is SEOURLKEYWORD_ID, the primary key column.  This makes it sound like the update statement I'm trying to run is attempting to insert a row that has a SEOURLKEYWORD_ID that already exists in the table.  </p>
  
  <p>If I run a select * statement on the rows I'm trying to update, here's what I get:</p>
  
  <pre><code>select * from SEOURLKEYWORD 
  where storeent_id = 10701 
  and lower(URLKEYWORD) like '%/%';
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/YzOV8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/YzOV8.png" alt="enter image description here"></a></p>
  
  <p>I don't understand how executing the UPDATE statement is resulting in an error here.  There are only 4 rows this statement should even be looking at, and I'm not manually updating the primary key at all.  It kind of seems like it's reinserting a duplicate row with the updated column value before deleting the existing row.  </p>
  
  <p>Why am I seeing this error when I try to update the URLKEYWORD column of these four rows?  How can I resolve this issue?</p>
  
  <p>IMPORTANT:  As I wrote this question, I have narrowed down the problem to the last of the four rows in the table above, SEOURLKEYWORD_ID = 3074457345616973668.  I can update the other three rows just fine, but the 4th row is causing the error, I have no idea why.  If I run a select * from SEOURLKEYWORD where SEOURLKEYWORD_ID = 3074457345616973668;, I see only the 1 row.</p>