Question Re: Syntax for UPSERT using a subquery in Technology Q&A

Syntax for UPSERT using a subquery

justin_molenaur2 — Tue, 09 Sep 2014 20:42:15 GMT

Hi all, I can't seem to figure this one out so maybe someone has hit the same issue. I am guessing it's a syntax problem.

UPSERT works fine when dealing with a single row, but I can't get this to work for a subquery.

Works fine

UPSERT <TABLE> (COLUMN1, COLUMN2) VALUES ('VALUE1', 'VALUE2') WITH PRIMARY KEY;

Syntax error near "PRIMARY"

UPSERT <TABLE> (COLUMN1, COLUMN2) VALUES (SELECT COLUMN1, COLUMN2 FROM TABLE2) WITH PRIMARY KEY;

Same thing tried another way

UPSERT <TABLE> (COLUMN1, COLUMN2) (SELECT COLUMN1, COLUMN2 FROM TABLE2) WITH PRIMARY KEY;

I have used this successfully before with INSERT, but I am thinking the UPSERT is a slightly different animal.

Regards,

Justin

Re: Syntax for UPSERT using a subquery

Former Member — Wed, 10 Sep 2014 02:40:06 GMT

Hi Justin,

First you can find the syntax here. REPLACE | UPSERT - SAP HANA SQL and System Views Reference - SAP Library

> UPSERT <TABLE> (COLUMN1, COLUMN2) VALUES ('VALUE1', 'VALUE2') WITH PRIMARY KEY;

It's correct.

> UPSERT <TABLE> (COLUMN1, COLUMN2) VALUES (SELECT COLUMN1, COLUMN2 FROM TABLE2) WITH PRIMARY KEY;

It's incorrect. Should be UPSERT <TABLE> (COLUMN1, COLUMN2) SELECT COLUMN1, COLUMN2 FROM TABLE2;

> UPSERT <TABLE> (COLUMN1, COLUMN2) (SELECT COLUMN1, COLUMN2 FROM TABLE2) WITH PRIMARY KEY;

It's incorrect. Should be UPSERT <TABLE> (COLUMN1, COLUMN2) SELECT COLUMN1, COLUMN2 FROM TABLE2;

Best regards,

Wenjun

Re: Syntax for UPSERT using a subquery

justin_molenaur2 — Wed, 10 Sep 2014 13:32:11 GMT

Thanks Wenjun - of course I read the help docs before posting a question

That being said, there are no real examples of what I am trying to do. What I was missing was that the primary key handling is implicitly defined when operating on a set basis. The key point is that the columns that the full primary key must be included in the column list.

Regards,

Justin

Re: Syntax for UPSERT using a subquery

lbreddemann — Wed, 10 Sep 2014 13:39:28 GMT

Hey Justin,

what exactly is it you want to do though?

- Lars

Re: Syntax for UPSERT using a subquery

justin_molenaur2 — Wed, 10 Sep 2014 13:44:52 GMT

Hey Lars, there's nothing complex here - just trying to UPSERT a subquery. It's working fine now, just a minor syntax issue.

Now looking back at the help page, the very last statement would be the best example. However, it was not clear to me that the "WITH PRIMARY KEY" clause is not required for a subquery.

Cheers,

Justin

Re: Syntax for UPSERT using a subquery

lbreddemann — Wed, 10 Sep 2014 13:48:15 GMT

Don't worry - I had to fiddle with this for quite some time as well.

It's a really underused statement and unfortunately far from MERGE on other platforms...

Cheers, Lars

Re: Syntax for UPSERT using a subquery

justin_molenaur2 — Wed, 10 Sep 2014 13:55:26 GMT

So I guess that only on a single row update basis ('VALUES' statement) is the "WITH PRIMARY KEY" clause required?

Is it your understanding that a subquery should automatically honor the primary keys of the target table and either INSERT or UPDATE accordingly?

Regards,

Justin

Re: Syntax for UPSERT using a subquery

Former Member — Thu, 11 Sep 2014 02:57:44 GMT

Hi Justin,

> So I guess that only on a single row update basis ('VALUES' statement) is the "WITH PRIMARY KEY" clause required?

"WITH PRIMARY KEY" is not a must on a single row update. You can use WHERE <condition> instead. Sometimes you use neither WHERE <condition> nor WITH PRIMARY KEY

Syntax:

UPSERT <table_name> [ <column_list_clause> ] { <value_list_clause> [ WHERE <condition> | WITH PRIMARY KEY ] | <subquery> }

> Is it your understanding that a subquery should automatically honor the primary keys of the target table and either INSERT or UPDATE accordingly?

Yes; otherwise the behavior is weird and the logic is incorrect.

For better understanding, I created some examples for you. Hope you can understand UPSERT/REPLACE better.

Example 1: Without primary key

CREATE COLUMN TABLE U (ID INTEGER, VAL INTEGER);

UPSERT U VALUES (1, 1); -- Table U has <1, 1>

UPSERT U VALUES (2, 2); -- Table U has <2, 2>

UPSERT U VALUES (3, 3); -- Table U has <3, 3>

UPSERT U (VAL) VALUES (4); -- Table U has <3, 4>

UPSERT U VALUES (1, 2) WHERE ID = 1; -- Table U has <3, 4> and <1, 2>

UPSERT U (VAL) VALUES (5); -- Table U has <3, 5> and <1, 5>

UPSERT U VALUES (10, 10); -- Table U has <10, 10>, <10, 10>

Example 2: With primary key

CREATE COLUMN TABLE UU (ID INTEGER PRIMARY KEY, VAL INTEGER);

UPSERT UU VALUES (1, 1); -- Table U has <1, 1>

UPSERT UU VALUES (2, 2); -- Table U has <2, 2>

UPSERT UU VALUES (3, 3); -- Table U has <3, 3>

UPSERT UU (VAL) VALUES (4); --ERROR: cannot insert NULL or update to NULL: ID. In this case, you need to include primary key.

UPSERT UU (ID, VAL) VALUES (3, 4); -- Table U has <3, 4>

UPSERT UU VALUES (1, 2) WHERE ID = 1; -- Table U has <3, 4> and <1, 2>

UPSERT UU (VAL) VALUES (5); --ERROR: cannot insert NULL or update to NULL: ID. In this case, you need to include primary key.

UPSERT UU (ID, VAL) VALUES (1, 5); --ERROR: unique constraint violation. The reason is very simple. Table U cannot own two <1, 5>

UPSERT UU VALUES (10, 10); --ERROR: unique constraint violation. The reason is very simple. Table U cannot own two <10, 10>

UPSERT UU VALUES (1, 10) WITH PRIMARY KEY; --Equal with UPSERT UU VALUES (1, 10) WHERE ID = 1. Table U has <3, 4> and <1, 10>

UPSERT UU VALUES (3, 10) WITH PRIMARY KEY; --Equal with UPSERT UU VALUES (1, 10) WHERE ID = 3. Table U has <3, 10> and <1, 10>

Best regards,

Wenjun

Re: Syntax for UPSERT using a subquery

Former Member — Wed, 12 Aug 2015 10:47:30 GMT

It is absolutely purposeless ..when it says unique constraint violation during upsert operation!! Lol ...that is exactly when upsert has to decide and do an update operation on the row in question ...the very purpose of doing upsert is missing

Re: Syntax for UPSERT using a subquery

lbreddemann — Wed, 12 Aug 2015 11:26:18 GMT

Not sure what's your point here.

If you really feel that re-surfacing this old discussion then please provide some context.

What is not working for you with the UPSERT command?

What do you expect it to do?

Could it be that you don't really understand what the reason for the unique constraint violation in Wenjun's example was?

It's definitively correct and works as expected.

Re: Syntax for UPSERT using a subquery

former_member698728 — Mon, 25 Nov 2019 14:55:03 GMT

Actually there is a better way of doing Upsert. Try the MERGE statement. It does the same Update or Insert, with easily coding.

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/3226201f95764a57810dd256c9524d56.html?q=MERGE

Re: Syntax for UPSERT using a subquery

chindrism96 — Tue, 09 Jan 2024 14:11:42 GMT

Why exactly does the 'with primary key' not work with subqueries? I can't seem to find any explanation online.

Best regards,

Mihai