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.
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);
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.