UPDATE (Upsert Form) Examples| Teradata Vantage - Examples: UPDATE (Upsert Form) with PTI Tables - Advanced SQL Engine

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

UPDATE (Upsert Form) Examples| Teradata Vantage - Examples: UPDATE (Upsert Form) with PTI Tables - Advanced SQL Engine - Teradata Database

Time Series Tables and Operations

Examples: Upsert with Storage Distribution Based on Time Bucket

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

Examples: Upsert with Storage Distribution Based on Columns Clause