How does upsert or Update exisiting or insert new recs work

[dsnovice]

I have two parallel streams in a server job writting records to common table. The two streams have the same set of keys (Key 1,Key 2), but populate some over lapping fields (LOAD_DATE) and two stream specific fields (SRC & TGT).

The problem is, lets say there are 50 records from stream 1 and 50 records from stream two, Stream 1 is able to update or insert all 50 records and Stream 2 manages to update or insert only 48. Two records are not updated.

Data Stream 1

Key 1, Key 2, SRC_AMT, SRC_PRODUCT, LOAD_DATE
1 A 100.00 Type 1 Nov 1st, 2009
2 A 200.00 Type 2 Nov 1st, 2009
....
....

Data Stream 2

Key 1, Key 2, TGT_AMT, TGT_PRODUCT, LOAD_DATE
1 A 100.00 Type 1 Nov 1st, 2009
2 A 200.00 Type 2 Nov 1st, 2009
....
....

When I check in the table

Key 1, Key 2, SRC_AMT, SRC_PRODUCT, TGT_AMT, TGT_PRD, LOAD_DT
1 A 100.00 Type 1 100.00 Type 2 Nov 1st, 2009
2 A 200.00 Type 2 (null) (Null) Nov 1st, 2009

there are No warning messages in the job logs, indicating any errors or rejects.

So back to my question, how does upsert or Update existing or Insert new rows work in the back end?
1. Does it execute a select statement on the table to check if the record exists, if it does then insert else send in update statement?
2. Does it simply execute the update statement, if it gives a negative return code then execute the insert statement?

Thank you,

a novice..

[ray.wurlod]

No documented information is available on this quesion. I used to believe that your second option was the way it works, but have no way to be certain.

[chulett]

For any of the 'combo' actions, the first action is executed and if it fails, then the second is executed. The update "fails" if it updates zero records.

[dsnovice]

The dba says he doesn't see any rejects or negative codes. Plus he said the transaction Isolation levels, like cursor stability to Repeatable read only are effective on Selects. Since they have the same choices in Input side, I thought there was a select done some where..

Thank you for clarifying the doubt.. I will assume its as you guys said.

Thank you,

A novice