One or more values in the INSERT statement, UPDATE statement, or forei

[shann0nw]

I get this error when updating a database I am working with (DB2 V7).

I loop through the contents of a file and based on a flag U = Update,
N = Insert & D = Delete, I perform the action on the item with the
table.

The table is three rows in length: id, part_number, qty.

I have a unique index on id, part_number, qty.

If the flag = U then I try to update the row.
If the update fails, I set the falg to N and try to insert the new
row.

If the flag = D then I delete the row.

If the flag = N I try to insert the row.
If that fails I catch the error and attempt an update.

A Problem appears to occur when the flag = N to Insert. All inserts
are failing with the [IBM][CLI Driver][DB2/SUN] SQL0803N error
described in the subject.

My problems with this are:

1. If I catch the error then it would mean that the row already exists
and I try the subsequent processing.
2. The update then throws a non unique row error.
3. When finished, none of the rows that errored are existing in the
database so I am at a loss to figure out where the part went and why
it errored!!!

How can this be the case????

Shannon

 

[Dagon]

Could it be that you have duplicates in the input file itself i.e. some of the 'N' records occur multiple times. The first one is probably inserted correctly but the subsequent ones fail, causing the procedure to fail and rollback.

 

[MarcLodge]

Hi Shannon,
I would have a look at your error trapping to check that it is working correctly, as the error (-803) is for a duplicate row on an insert. Presumably this is what you are checking for.

I would also analyse a 'normal' run of data to see if you should be:
Updating and Inserting
or
Inserting and Updating

The most efficient method will depend on your 'normal' run.

Hope this helps

Marc