cancel
Showing results for 
Search instead for 
Did you mean: 

Syntax for UPSERT using a subquery

justin_molenaur2
Contributor
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

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

justin_molenaur2
Contributor
0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

Hey Justin,

what exactly is it you want to do though?

- Lars

justin_molenaur2
Contributor
0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

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

justin_molenaur2
Contributor
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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 

lbreddemann
Active Contributor
0 Kudos

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.

chindrism96
Advisor
Advisor
0 Kudos

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

Best regards,

Mihai

Answers (1)

Answers (1)

0 Kudos

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/3226201f95764a57810dd256c9...