on 09-09-2014 9:42 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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.
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.