Example: Upsert Specifying Different Tables - Teradata Vantage

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.