MERGE vs UPSERT

Database
WAQ
Enthusiast

MERGE vs UPSERT

what is the difference between MERGE and UPSERT?
which one of them is supported in Teradata?
11 REPLIES
Enthusiast

Re: MERGE vs UPSERT

From what I understand, Merge is SQL used to upsert data. Merge is a valid SQL command in databases such as Oracle. Never came across upsert as a valid sql command.

Coming to TD, I am not familiar with merge command. This may work for you:

UPDATE tname
SET cname = expr [... , cname = expr]
[ WHERE cond ]
ELSE
INSERT INTO tname [ (colname [... , colname] ) ]
VALUES (expr [... , expr])

A simple test with above SQL syntax can verify if this works for you.
Enthusiast

Re: MERGE vs UPSERT

Teradata (as of release 12.0) supports both the ISO/ANSI standard SQL MERGE statement and the UPDATE...ELSE INSERT syntax. That latter is commonly known as "upsert".
WAQ
Enthusiast

Re: MERGE vs UPSERT

Okay so teradata supports both but still can understand that whats the difference between the two?
Enthusiast

Re: MERGE vs UPSERT

The MERGE statement is more general. It supports all the functionality of upsert and has some additional capabilities. MERGE is supported by the ANSI/ISO SQL standard, so it is more portable.
WAQ
Enthusiast

Re: MERGE vs UPSERT

UPSERT is only the concept and MERGE is the implementation of this concept. Right?

I found MERGE syntax in many places but could not able to find any syntax for UPSERT thats probably because an UPSERT is really just an UPDATE combined with an INSERT. Is it like this?
Enthusiast

Re: MERGE vs UPSERT

you are correct... UPSERT is a concept using which you can attain "Slowly Chaging Dimension type-1". Which basically says that if a row exists update it with new data, if its not existing already, then insert it.
Its a concept but not a SQL command.

MERGE INTO is a SQL command using which you can achieve the UPSERT operation.
WAQ
Enthusiast

Re: MERGE vs UPSERT

i think this really clear my concept regarding UPSERT and MERGE
thanks all.

Re: MERGE vs UPSERT


http://www.teradatau.courses.teradata.com/learning/BLADE_MS/legacy/29957_SQL_ApplDev/10FeaturesV2R4....

An 'Upsert' is an operation which performs either a row update or a row insert, depending on the pre-existence of the row. There is no 'UPSERT' command as such, rather the Upsert process is an extended form of the UPDATE command.

There are some key rules governing the use of this form:

The INSERT and UPDATE must reference the same single row of the same table.
The UPDATE must use the Primary Index of the table.
The target table may be a table or a view.

The simplest syntax of the Upsert form of the UPDATE command is as follows:

UPDATE table1 SET col_a = value_a
WHERE PI_col = PI_value ELSE
INSERT INTO table1 VALUES (PI_value,value_a);

Enthusiast

Re: MERGE vs UPSERT

How can i do an upsert into teradata table(DEV) by comparing PRD table. Looking for a way to synch DEV table to PRD table and make them identical as there are some changes made to the existing records and new records were also added.