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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

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.