17.05 - Example: Upsert Specifying Different Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
Release Date
January 2021
Content Type
Programming Reference
Publication ID
English (United States)

This example shows an upsert UPDATE request that does not specify the same table name for both the UPDATE part and the INSERT part of the request.

     UPDATE sales
     SET item_count = item_count + 1
     WHERE (item_nbr = 10
     AND    sale_date = '05/30/2000')
     ELSE INSERT INTO new_sales (10, '05/30/2000', 1);

One of the rules of the upsert form of UPDATE is that only one table is processed for the request. Because the tables sales and new_sales are not the same for this example the system returns an error to the user indicating that the name of the table must be the same for both the UPDATE and the INSERT.