How Upsert Works in Primary key based tables

[jyoti_senapati]

Hi Experts,

I need the information/mechanism on which basis upsert work (picks up the latest record).

Scenario1:-

Below Table T_PRE(Row based Table) has below values

screen-shot-2018-05-02-at-123133-pm.png

After the insert into the Final table where "KEY" column is Primary key. The data output is like below

screen-shot-2018-05-02-at-123355-pm.png

Scenario 2:-

Below Table T_PRE_COL(Column based Table) has below values

screen-shot-2018-05-02-at-123537-pm.png

After the insert into the Final table where "KEY" column is Primary key. The data output is like below

screen-shot-2018-05-02-at-123620-pm.png

So my doubt is mentioned below

1. Is the Upsert mechanism is same if data comes from Row based table or column based table

2. How the HANA Engine defines which is the latest record in both the tables (is there any internal sequential index is generated?)

Please provide your feedback /input.

Regards,

Jyoti

[lbreddemann]

Please read the documentation on UPSERT first.

This command is not a bit about the "latest record". It's UPDATE OR INSERT - that's it.

If your primary key is not unique then it's not a primary key and any update would affect all matching records.

And yes, the command is the same for all tables and no, if YOU don't specify an order, then the database doesn't create one for you. No sequential index magic there.