running a delete statement that matches multiple rows fails orpartially fails to delete those rows.

I am having a very strange issue and looking for some ideas on next
troubleshooting steps.

Summary:
running a delete statement that matches multiple rows fails or
partially fails to delete those rows.

Description:
I am running Sybase ASE 15.0.0 and Solaris 10 with multiple zones. I
BCP 1.5 million rows of
data into a ProductStage table before running the following two
statements -

DELETE Product
FRM Product,
ProductStage (INDEX XPKProductStage)
WHERE Product.BatchNumber LIKE @LikeArgument
AND Product.BatchNumber = ProductStage.BatchNumber
AND Product.Month = ProductStage.Month
AND Product.Agency = ProductStage.Agency


INSERT INT Product(BatchNumber,Month,Agency,Address,CRP,CAM,W AL)
SELECT BatchNumber,Month,Agency,Address,CRP,CAM,WAL
FRM ProductStage (INDEX XPKProductStage)
WHERE BatchNumber LIKE @LikeArgument

Note: batch numbers are three characters followed by three digits
like the following XFD001, XFD002, XFD003 so
a specific example of the run may look like this -

DELETE Product
FRM Product,
ProductStage (INDEX XPKProductStage)
WHERE Product.BatchNumber LIKE 'XFD%'
AND Product.BatchNumber = ProductStage.BatchNumber
AND Product.Month = ProductStage.Month
AND Product.Agency = ProductStage.Agency


INSERT INT Product(BatchNumber,Month,Agency,Address,CRP,CAM,W AL)
SELECT ProductNumber,Month,Agency,Address,CRP,CAM,WAL
FRM ProductStage (INDEX XPKProductStage)
WHERE ProductNumber LIKE 'XFD%'

There are around 1500 different three character prefixes.

I can usually run this process about 10 times each time with similar
if not identical data without an issue. The process being
BCPing 1.5 million records into the ProductStage and then running
the delete and insert statements for each 'Batch'.
There are usually around 1500 batches with about 1000 rows each.
Eventually I will get the following error -
"Attempt to insert duplicate key row in object 'Product' with unique
index 'XPKProduct'". First how is that possible given
the previous delete and insert statements? The delete should make
sure that there are no duplicated records in the Cusomter table.

After getting this error once, the loads will always return the
duplicate index error for the same Batch. I can get it to work again
by BCPing out the data that is in Product and ProductStage, dropping
and recreating the tables, BCPing the data back into those tables
The next time I run the staging process it will run without issue
and work for around another 10 trys.

I did some more research and by running the delete and insert
manually for a given batch (LIKE 'XFD%') after getting the
"Attempt to insert duplicate key row in object 'Product' with unique
index 'XPKProduct'". error I found that sometimes it fails to
delete or sometime it will partially delete the rows in Product that
match the WHERE Product.BatchNumber LIKE 'XFD%'. In one particular
case there was 879 rows in bacth "LIKE 'XFD%'" running the delete
statement using isql would return (734 rows affected). I would then
run
the delete statement again and it would report (145 rows affected).
It would take two runs of the delete statement to delete the full
879 rows it should have!

I ran DBCC on the involved tables and they check out fine. I am at a
loss and not sure where to go from here. Any ideas?

Instead of dialect, have you considered Standard SQL and using
IS naming conventions? For example MNTH is a reserved word,
agency could be anything from agency_size, agency_type, agency_name,
etc.

DELETE FRM Products
WHERE Products.batch_nbr LIKE @like_argument
AND EXISTS
(SELECT *
FRM ProductStages AS S
WHERE Product.batch_nbr = S.batch_nbr
AND Product.something_month = S.something_month
AND Product.agency_something = S.agency_something);

This will avoid a duplicate row problem you will find with the dialect
syntax.