UPSERT in SAP HANA

UPSERT: The UPSERT is used to either updates rows in a table or inserts new rows if the record doesn’t exist with the specified condition.

Syntax: UPSERT(or REPLACE) <schema_name>.<table_name> VALUES (values) WHERE <condition> ;

Points to remember:

1. Whenever this command is used without a subquery it functions in a similar way to the UPDATE statement. The difference with this command is that when the WHERE clause condition is false it will insert a new record into the table.
2. When this command is used with a table that has a PRIMARY KEY, the primary key column must be included in the column list. Columns defined with NOT NULL and without a default specification also have to be included in the column list. Columns that are not specified will be filled with a default value or NULL.
3. The UPSERT (or REPLACE) statement with a subquery functions like the INSERT statement. The exception with this command is that, if an existing row in the table has the same primary key value as a new row, the row will be updated with the returned record from the subquery. If the table does not have a primary key the command functions in an equivalent way to an INSERT statement, as an index cannot be used to check for row duplication.

Examples:

1. Insert a record into the table using UPSERT.

Ans: UPSERT "KABIL_PRACTICE"."ORDERS"
                    VALUES (1234,70)   — Columns are Order_Number and Quantity

2. Insert the record if where clause if false or update the row if where clause is true.

Ans: UPSERT "KABIL_PRACTICE"."ORDERS"
                    VALUES (1235,120) WHERE ORD_NO = 1235

3. Update the existing record with primary key constraint.

Ans: UPSERT "KABIL_PRACTICE"."ORDERS"
                    VALUES (1234,170) WITH PRIMARY KEY

4. Update or insert the records using sub query.

Ans: UPSERT "KABIL_PRACTICE"."ORDERS"
                    SELECT 1236,40
                    FROM DUMMY 

Examples:

Create Order table:

create column table "KABIL_PRACTICE"."ORDERS"

(

"ID" integer,

"QTY" integer

)

;

Insert records in the table:

insert into "KABIL_PRACTICE"."ORDERS" Values (1,100);

insert into "KABIL_PRACTICE"."ORDERS" Values (2,150);

insert into "KABIL_PRACTICE"."ORDERS" Values (3,160);

Result:

ORDERS_TABLE

Now, Try this code 1:

upsert "KABIL_PRACTICE"."ORDERS" values (4,180) where "ID" = 2;

Result:

Updated Table

Note: It's just updated a record where “ID” = 2, because where the condition is True.

Code 2:

upsert "KABIL_PRACTICE"."ORDERS" values (2,280) where "ID" = 2;

Results:

Inserted Table

Not just inserts a new record because where condition is false.

Code 3:

upsert "KABIL_PRACTICE"."ORDERS" values (7,366);

Results:

All values changed

Note: Here all the values are updated, Because I didn’t mention a condition.