UPSERT without Primary Key

[Former Member]

Is it possible to do an UPSERT with a subquery without referencing the Primary Key? From the documentation it seems like this would just lead to an insert:

"The UPSERT or REPLACE statement with a subquery works like the INSERT statement, except that if an old row in the table has the same value as a new row for a PRIMARY KEY, then the old row is changed by values of the returned record from a subquery. Unless the table has a PRIMARY KEY, it becomes equivalent to INSERT because there is no index to be used to determine whether or not a new row duplicates another."

Is there an equivalent for this?

create column table table1 (key1 varchar2(10) primary key, field1 varchar2(10), field2 varchar2(10));
create column table table2 (key1 varchar2(10) primary key, field1 varchar2(10), field2 varchar2(10));
insert into table1 values ('1', 'test1', 'test1');
insert into table2 values ('2', 'test1', 'test2');
merge into table1 t1 using (select key1, field1, field2 from table2) t2 on (t1.field1 = t2.field1)
when matched then
update set t1.field2 = t2.field2 
when not matched then insert (t1.key1, t1.field1, t1.field2) values (t2.key1, t2.field1, t2.field2);

So field2 for the single row in table1 would be updated based on the join to field1 rather than the primary key.

Thanks,

Jason

[Former Member · Accepted solution]

Hi Jason,

   The short answer is yes to your first question - you end up with an inserted row in the case of no PK in thre sub-select. Check out http://help.sap.com/hana/html/sql_replace_upsert.html for the details. With WRT not having a PK on a table, the relational database part of me questions why you wouldn't have  a PK defined on the table. Note: if you are using a COLUMN table with the HISTORY option, all updates are inserts.

Regards,

Bill

[Former Member · Accepted solution]

Hi Bill,

Thank you for the response. In this case we do have a PK defined on the table, but for this MERGE case we want to use non-PK fields to evalute whether to INSERT or UPDATE.

Thank you,

Jason

[lbreddemann · Accepted solution]

Hi Jason,

although they look very similar on first sight, UPSERT and MERGE are profoundly different.

While MERGE offers a vast range of options on what to do when matching records are found (or not), UPSERT really behaves a bit like the 'dumb little brother',

UPSERT/REPLACE really is about a very specific use case where you have unique records (to make it easier, this is why the primary key must be present - and no, unique key is not sufficient here, think of NULLs!) that you want to update or insert.

But apart from that, could you explain a bit more what your scenario is about?

When you don't use the primary key as the base for the merge, what should happen if you e.g. find that 3 rows should be updated?  Update all three of them?

Lars

[Former Member · Accepted solution]

Our current application tables are using a surrogate key (generated by our platform rather than by the database) as the primary key, but we also have unique keys aside from the primary key. The data which we are updating is coming from outside systems with no knowledge of our surrogate keys, so the new data to be updated is identified by the unique keys.

Currently we import this new data into a temporary table and then MERGE with the target table.

It would be possible to join back from the target table to update the temporary table with the primary keys based on the surrogates and then UPSERT back into the target table, but this would add additional steps and probably increase the processing time for the data.

[lbreddemann · Accepted solution]

Jason Dy-Johnson wrote:
Our current application tables are using a surrogate key (generated by our platform rather than by the database) as the primary key, but we also have unique keys aside from the primary key. The data which we are updating is coming from outside systems with no knowledge of our surrogate keys, so the new data to be updated is identified by the unique keys.

And DB-abstraction has a new victim. sorry - couldn't resist.

This is really too bad now, as the UPSERT is simply not flexible enough to deal with what would be called "key-candidates" in relational database design.

What I would do to make this work (assuming the application table is stable):

Drop the current primary key.

Create a unique index and a NOT NULL constraint on the column of the former primary key.

Now, create a new primary key for the columns you actually use to identify the records.

There's nevertheless a whole in your data loading plan: what do you do with the rows that should be INSERTed?

How do these rows get their generated "framework" primary key?

[Former Member · Accepted solution]

In our temp table we are populating a generated artificial primary key. Only on an INSERT will this be used. So in the below example the WHEN MATCHED updates the non-key fields (excluding primary and unique), but the WHEN NOT MATCHED INSERT clause includes all of the fields:

merge into table1 t1 using (select key1, field1, field2 from table2) t2 on (t1.field1 = t2.field1) when matched then update set t1.field2 = t2.field2 when not matched then insert (t1.key1, t1.field1, t1.field2) values (t2.key1, t2.field1, t2.field2);

[lbreddemann · Accepted solution]

hmm... that's even worse.

This is something you cannot do with the UPSERT command.

That's why REPLACE is the better name for this command - it looks up if it already has a record with the same primary key and replaces all the other values, respectively inserts the record.

There is no fancy split logic built in there.

Looks like you'll have to resort to manual SQLScript for that...

First find and update the rows that are already there and do the insert later on with the remaining rows.

cheers,

Lars

[rama_shankar3 · Accepted solution]

Good info guys - thanks!

[justin_molenaur2 · Accepted solution]

Lars, can you point me in the direction of any documentation on the MERGE DML statements? I am looking in the SQL reference in help and can't find anything with regard to MERGE. I can see DELTA MERGE and UPSERT, but  I am specifically interested in the branching capabilities of the MERGE statement.

Regards,

Justin

[lbreddemann · Accepted solution]

Hey Justin

Sorry to disappoint you on this one, but SAP HANA doesn't support the MERGE command yet - not sure it ever will.

I referred to it earlier as it is well known on other DBMS platforms and used it as a reference point.

- Lars

[justin_molenaur2 · Accepted solution]

Ok - so if I am looking for logic like below for inserting/updating to a target table from some source, I am guessing I'll just have to branch an Insert and an update statement in SQLScript to pull it off?

- If a source record with the same primary key values exists in the target, update only the measure columns, updating the measures with the sum of the old value PLUS the new value
- If a source record with primary key does not exist in the target, insert the record.

This would be like an UPSERT, but the update should be additive to the existing record.

Thoughts?

Regards,

Justin

[lbreddemann · Accepted solution]

That would be the classic BW request compression for infocubes (F-fact table records being merged into E-fact table records).

You can try this like shown below. Might not be the best performing solution, but provides the functionality.

/*http://scn.sap.com/message/14805720#14805720*/

create column table target (id integer primary key, val decimal (10,2))

create column table source (id integer , val decimal (10,2))

insert into target values (1, 5.5);

insert into source values (1, 5.5);

insert into source values (2, 3);

insert into source values (2, 3);

select * from target

/*

ID|VAL

1 |5.50

*/

select * from source

/*

ID|VAL

1 |5.50        + 5.5

2 |3.00        + 3

2 |3.00        + 3 => + 6

*/

upsert target

select s.id, ifnull(t.val, 0) + ifnull(s.val,0)

from target t right outer join source s

on t.id = s.id

select * from target

/*

ID|VAL 

1 |11.00

2 |3.00

--> NOT what we wanted!!

*/

upsert target

select s.id, sum(ifnull(t.val, 0) + ifnull(s.val,0))

from target t right outer join source s

on t.id = s.id

group by s.id

/* we want 1 => + 5.5 and 3  => +6*/

select * from target

/*

ID|VAL 

1 |16.50

2 |12.00

*/

- Lars

[justin_molenaur2 · Accepted solution]

Thanks Lars. Also very similar to DSO key figure update mode of SUM as opposed to OVERWRITE.

Regards,

Justin