Results 1 to 4 of 4

Thread: insert question

  1. #1
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42

    Unanswered: insert question

    Hey,

    I am trying to update a particular field in a table that is part of the Primary Key for that table. There are 9,386 rows I am trying to update. The current value in the field is 0, and I am trying to turn those values to 2004. When I run the update statement, I get this:

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    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 "6" constrains
    table "GLCONSOL" from having duplicate rows for those columns.
    SQLSTATE=23505

    How is this accomplished?
    Thx!

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Primary key are unique by definition. You are changing one or more rows that are trying to make a non-unique primary key value.

    Andy

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by rvsc36
    Hey,

    I am trying to update a particular field in a table that is part of the Primary Key for that table. There are 9,386 rows I am trying to update. The current value in the field is 0, and I am trying to turn those values to 2004. When I run the update statement, I get this:

    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    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 "6" constrains
    table "GLCONSOL" from having duplicate rows for those columns.
    SQLSTATE=23505

    How is this accomplished?
    Thx!
    Check the docs for SQL0803... "db2 ? sql0803", it has this useful query for determining which unique index you're tripping over:

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

    Once you know the index, check the columns and make sure you're update will keep that set of columns unique for all rows.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42
    Thanks for all of the information. I appreciate everyone's input.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •