UPSERT in SAP HANA 2.0 SPS5 without updating a particular column.

[former_member565459]

Hello,

I've a requirement where I want to insert/update into a table based on a primary key.

I've one column named RECORD_DATE whose default value I've set to current_timestamp.

Now when I'm getting a new record on the same primary key, I don't want to update the record_date column with current_timestamp.

In current scenario, my record_date column also gets updated to current_timestamp.

Is there a way on how can I achieve this?

CREATE COLUMN TABLE PH_UPSERT_CHECK(
ID INT,
NAME NVARCHAR(10),
RECORD_DATE SECONDDATE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT CPK_UP PRIMARY KEY(ID));

UPSERT PH_UPSERT_CHECK(ID,NAME)VALUES(1,'PH') WITH PRIMARY KEY;

1. When I run this record on an empty table, record_date should get updated(as it is insert)

2. When I re-run this record on the table for the next time with some change in column NAME (update)

the record_date column shouldn't be updated.

florian.pfeffer can you help me with the same?

TIA.

Regards,

Prathamesh H.

[pfefferf]

Following comments/question:

- Why the RECORD_DATE is the primary key and not the ID?

- When you do a second time the UPSERT for the "same" record, not the original created data record is updated, but a new one is created, right?

[former_member565459]

florian.pfeffer, thankyou for your swift response.

1. It was a mistake done while posting the question. I've updated the question with primary key on ID.

2. My understanding is when we are running UPSERT on primary key, for the same primary key (in this case ID = 1) the same record should be updated for the columns given not changing the value of record_date column, and if a new primary arrives(say ID =2) in that case a new record should be inserted with record_date as current_timestamp.

This used to work as expected in Hana 1.0 SPS 12

We are facing this changed behaviour recently when we upgraded to a newer version.

[RichS]

Use MERGE INTO instead with the WHEN MATCHED and WHEN NOT MATCHED clauses

[former_member565459]

fartsmeller

Thankyou for your response. And yes, MERGE works perfectly, we've tried using this.

But I want to understand why this doesn't work with the UPSERT statement.

[pfefferf · Accepted solution]

prathamesh_h As I don't know the internal implementation of the command I cannot say why exactly this happens, but I can say under which condition it happens. And it seems that the behaviour is related to the "WITH PRIMARY KEY" clause.

If you do the statement with "WITH PRIMARY KEY" clause the RECORD_DATE column is updated with a "new" default value:

UPSERT PH_UPSERT_CHECK(ID,NAME) VALUES (1,'PH') WITH PRIMARY KEY;<br>

If you don't use the "WITH PRIMARY KEY" clause and just use a "WHERE" clause for the primary key, the RECORD_DATE column is not updated.

UPSERT PH_UPSERT_CHECK(ID,NAME) VALUES(1,'PH') WHERE ID = 1;<br>

An insert is done too if no record with the ID specified in the WHERE clause is existing.

An interesting behavior, for which I would expect an explanation by SAP as it is not really clear to me why the behavior is like it is.

PS: I tested this on SAP HANA Cloud, not on an On-Premise 2.0 SPS05 installation (maybe you can verify that on your system too).

[former_member565459 · Accepted solution]

Thanks, Florian.

Exactly, even I tried using the same way and the record date wasn't getting updated.

But, not very clear as to why there is such weird behaviour.