UPDATE (Upsert Form) Examples| Teradata Vantage - 17.00 - Examples: UPDATE (Upsert Form) with PTI Tables - Teradata Database

Teradata Vantageā„¢ - Time Series Tables and Operations

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1208-170K
Following are examples of update (upsert form) operations on a PTI table, with storage distribution based on:
  • Time bucket
  • Time bucket and columns clause
  • Columns clause

Examples: Upsert with Storage Distribution Based on Time Bucket

Here is the definition for the table used in this example. The nonsequenced PTI table ocean_buoy_no_seq includes:
  • Time zero specified as October 15, 2016, DATE '2016-10-15'
  • One hour time bucket, HOURS(1)
CREATE TABLE ocean_buoy_no_seq (
   TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
   TD_TIMECODE   TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN,
   salinity      INTEGER,
   temperature   INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', HOURS(1), NONSEQUENCED);
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 10:33:12.1', 23, 33);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 11:44:12.1', 24, 34);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 12:55:12.1', 25, 35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1' to set the salinity to 28:
UPDATE ocean_buoy_no_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1'
ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.1', 28, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6':
UPDATE ocean_buoy_no_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' 
ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.6', 28, 33);

Examples: Upsert with Storage Distribution Based on Time Bucket and Columns Clause

Here is the definition for the table used in this example. The nonsequenced PTI table ocean_buoy_no_seq includes:
  • Time zero specified as October 15, 2016, DATE '2016-10-15'
  • One hour time bucket, HOURS(1)
  • The buoy_id column in the PRIMARY TIME INDEX, COLUMNS(buoy_id)
CREATE TABLE ocean_buoy_no_seq (
   TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
   TD_TIMECODE   TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN,
   BUOY_ID       INTEGER,
   SALINITY      INTEGER,
   TEMPERATURE   INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', HOURS(1), COLUMNS(buoy_id), NONSEQUENCED);
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 10:33:12.1', 333, 23, 33);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 11:44:12.1', 444, 24, 34);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 12:55:12.1', 555, 25, 35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1' for buoy_id 333 to set the salinity to 28:
UPDATE ocean_buoy_no_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' AND
      buoy_id  = 333
ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.1', 333, 28, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6' for buoy_id 666:
UPDATE ocean_buoy_no_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' AND
      buoy_id = 666
ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.6', 666, 28, 33);
Here is the definition for the table used in this example. The sequenced PTI table ocean_buoy_seq includes:
  • Time zero specified as October 15, 2016, DATE '2016-10-15'
  • One hour time bucket, HOURS(1)
  • The buoy_id column in the PRIMARY TIME INDEX, COLUMNS(buoy_id)
CREATE TABLE ocean_buoy_seq (
   TD_TIMEBUCKET BIGINT NOT NULL GENERATED SYSTEM TIMECOLUMN,
   TD_TIMECODE   TIMESTAMP(6) NOT NULL GENERATED TIMECOLUMN,
   TD_SEQNO      INTEGER NOT NULL GENERATED TIMECOLUMN,
   buoy_id       INTEGER,
   salinity      INTEGER,
   temperature   INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(6), DATE '2016-10-15', HOURS(1), COLUMNS(buoy_id), SEQUENCED(200));
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 10:33:12.1', 1, 333, 23, 33);
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 11:44:12.1', 1, 444, 24, 34);
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 12:55:12.1', 1, 555, 25, 35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1' and TD_SEQNO =1 for buoy_id 333 to set the salinity to 28:
UPDATE ocean_buoy_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' AND
      TD_SEQNO = 1 AND buoy_id  = 333
ELSE INSERT INTO ocean_buoy_seq (TIMESTAMP '2016-10-16 10:33:12.1', 1, 333, 28, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6' with TD_SEQNO = 1 for buoy_id 666:
UPDATE ocean_buoy_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' AND
      TD_SEQNO = 1 AND buoy_id = 666
ELSE INSERT INTO ocean_buoy_seq (TIMESTAMP '2016-10-16 10:33:12.6', 1, 666, 28, 33);

Examples: Upsert with Storage Distribution Based on Columns Clause

Here is the definition for the table used in this example. The nonsequenced PTI table ocean_buoy_no_seq includes:
  • Time zero specified as October 15, 2016, DATE '2016-10-15'
  • The ocean_zone and buoy_id columns in the PRIMARY TIME INDEX, COLUMNS(ocean_zone, buoy_id)
CREATE TABLE ocean_buoy_no_seq (
   TD_TIMECODE   TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN,
   ocean_zone    CHAR(2),
   buoy_id       INTEGER,
   salinity      INTEGER,
   temperature   INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', COLUMNS(ocean_zone, buoy_id), NONSEQUENCED);
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 10:33:12.1', 'PC', 333, 23,33);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 11:44:12.1', 'AT', 444, 24,34);
INSERT INTO ocean_buoy_no_seq(TIMESTAMP '2016-10-16 12:55:12.1', 'IN', 555, 25,35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1' and ocean_zone = 'PC' for buoy_id 333 to set the salinity to 28:
UPDATE ocean_buoy_no_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' AND
        ocean_zone = 'PC' AND buoy_id = 333  
ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.1', 'PC', 333, 23, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6' and ocean_zone = 'PC' for buoy_id 666:
UPDATE ocean_buoy_no_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' AND
       ocean_zone = 'PC' AND buoy_id = 666
ELSE INSERT INTO ocean_buoy_no_seq (TIMESTAMP '2016-10-16 10:33:12.6', 'PC', 666, 28, 33);
Here is the definition for the table used in this example. The sequenced PTI table ocean_buoy_seq includes:
  • Time zero specified as October 15, 2016, DATE '2016-10-15'
  • The ocean_zone and buoy_id columns in the PRIMARY TIME INDEX, COLUMNS(ocean_zone, buoy_id)
CREATE TABLE ocean_buoy_seq (
   TD_TIMECODE   TIMESTAMP(1) NOT NULL GENERATED TIMECOLUMN,
   TD_SEQNO   INTEGER NOT NULL GENERATED TIMECOLUMN,
   ocean_zone    CHAR(2),
   buoy_id       INTEGER,
   salinity      INTEGER,
   temperature   INTEGER)
PRIMARY TIME INDEX (TIMESTAMP(1), DATE '2016-10-15', COLUMNS(ocean_zone, buoy_id), SEQUENCED(20));
These insert operations populate the table with rows of data:
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 10:33:12.1', 1, 'PC', 333, 23,33);
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 11:44:12.1', 1, 'AT', 444, 24,34);
INSERT INTO ocean_buoy_seq(TIMESTAMP '2016-10-16 12:55:12.1', 1, 'IN', 555, 25,35);
This statement updates the row where the TD_TIMECODE value matches TIMESTAMP '2016-10-16 10:33:12.1', TD_SEQNO = 1, and ocean_zone = 'PC' for buoy_id 333 to set the salinity to 28:
UPDATE ocean_buoy_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.1' 
AND TD_SEQNO = 1 AND ocean_zone = 'PC' AND buoy_id = 333  
ELSE INSERT INTO ocean_buoy_seq (TIMESTAMP '2016-10-16 10:33:12.1', 1, 'PC', 333, 23, 33);
This statement inserts a new row after not finding a match in the TD_TIMECODE column for the value, TIMESTAMP '2016-10-16 10:33:12.6', TD_SEQNO = 1, and ocean_zone = 'PC' for buoy_id 666:
UPDATE ocean_buoy_seq
SET salinity = 28
WHERE TD_TIMECODE = TIMESTAMP '2016-10-16 10:33:12.6' 
AND TD_SEQNO = 1 AND ocean_zone = 'PC' AND buoy_id = 666
ELSE INSERT INTO ocean_buoy_seq (TIMESTAMP '2016-10-16 10:33:12.6', 1, 'PC', 666, 28, 33);