I am calling an Update statement from Visual Basic and I am getting this error.

[IBM][CLI Driver][DB2] 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 "XMTC97A "
constrains table "0000000201" from having duplicate rows for those columns.
SQLSTATE=23505

This is my statement:
UPDATE NOTE_LISTS
SET NOTEDATE = '02/20/2006',
ENTR_BY = 'Stan L',
ESTM_CMLT = '01/01/2006',
ACTL_CMLT = '01/02/2006',
CMPL_BY = 'Bobby B',
NOTE = 'This is only a test,
POLICYNUM = 'P987654321',
POLICYDEC = 'Accounting'
WHERE NOTE_ID = 1

All I am doing is trying to update a record and not put in a duplicate record. Any help would be great.

If you look up the error 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 index-id constrains table table-name from having duplicate rows for those columns.
Explanation:

The INSERT or UPDATE object table table-name is constrained by one or more UNIQUE indexes to have unique values in certain columns or groups of columns. Alternatively, a DELETE statement on a parent table caused the update of a foreign key in a dependent table table-name that is constrained by one or more UNIQUE indexes. Unique indexes might support primary keys or unique constraints defined on a table. The statement cannot be processed because completing the requested INSERT, UPDATE or DELETE statement would result in duplicate column values.

Alternatively, if a view is the object of the INSERT or UPDATE statement, it is the table table-name on which the view is defined that is constrained.

If index-id is an integer value, the index name can be obtained from SYSCAT.INDEXES by issuing the following query:

SELECT INDNAME, INDSCHEMA
FROM SYSCAT.INDEXES
WHERE IID = <index-id>
AND TABSCHEMA = 'schema'
AND TABNAME = 'table'

where schema represents the schema portion of table-name and table represents the table name portion of table-name .

The statement cannot be processed. The table remains unchanged.
User Response:

Examine the definition for the index identified by index-id .

For an UPDATE statement, ensure that the specified operation is not itself inconsistent with the uniqueness constraint. If this does not show the error, examine the object table content to determine the cause of the problem.

For an INSERT statement, examine the object table content to determine which of the values in the specified value list violates the uniqueness constraint. Alternatively, if the INSERT statement contains a subquery, the object table contents addressed by that subquery must be matched against the object table contents to determine the cause of the problem.

For a DELETE statement, examine the identified dependent table for unique constraints on foreign keys that are defined with the rule ON DELETE SET NULL. This table has a foreign key column included in the identified unique index that cannot be set to null since there is already a null in the column for that table.

Federated system users: isolate the problem to the data source failing the request (refer to the Troubleshooting Guide to determine which data source is failing to process the SQL statement) and examine the index definitions and data for the conditions listed previously.

sqlcode : -803

sqlstate : 23505

Run the SQL in the error description and it will tell you where the duplicate is occurring.

Andy

Look at your unique indexes. You may have a unique index on POLICYNUM, and policy P987654321 is already there, for NOTE_ID = 2 (example).
Now, if you update the existing row with NOTE_ID = 1, and use the same policy number, you are creating a duplicate entry for this unique index.
Hence the 803 error.

HTH, Rob.