17.05 - Example: UPDATE With a Mutator SET Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The mutator SET clause syntax permits you to write UPDATE requests for structured UDTs in a form of shorthand notation. Consider the following structured data type definitions:

     CREATE TYPE person AS (
       last_name  VARCHAR(20),
       first_name VARCHAR(20),
       birthdate  DATE)
     …
     ;

     CREATE TYPE school_record AS (
       school_name VARCHAR(20),
       gpa         FLOAT)
     INSTANTIABLE
     …
     ;

     CREATE TYPE college_record AS (
       school  school_record,
       major   VARCHAR(20),
       minor   VARCHAR(20))
     INSTANTIABLE
     …
     ;

Suppose you have the following table definition:

     CREATE TABLE student_record (
       student_id  INTEGER,
       student     person,
       high_school school_record,
       college     college_record);
Without the mutator SET clause notation, UPDATEs must be written in forms of chained or nested mutator method invocations. For example:
  • The following UPDATE request changes student John Doe to Natsuki Tamura:
         UPDATE student_record
          SET student = student.Last_name('Tamura').First_name('Natsuki')
         WHERE student.First_name() = 'John'
         AND   student.Last_name() = 'Doe';
  • The following UPDATE request makes the following updates to the college record of student Steven Smith:
    • school_name = 'UCLA'
    • GPA = 3.20
    • major = 'Computer Science'
           UPDATE student_record
            SET college =
            college.school(college.school().school_name('UCLA')
            .GPA(3.20)).major('Computer Science')
           WHERE student.First_name() = 'Steven'
           AND   student.Last_name() = 'Smith';

Formulating the necessary chained or nested mutator method invocations can be very complex. However, you can use the mutator SET clause notation to make writing UPDATE requests for a structured type column simpler and more intuitive.

Consider these rewrites of the two previous UPDATE requests (these use the mutator SET clause notation):
  • The following UPDATE request changes student John Doe to Natsuki Tamura:
         UPDATE student_record
           SET student.Last_name = 'Tamura',
               student.First_name = 'Natsuki',
         WHERE student.First_name() = 'John'
         AND   student.Last_name() = 'Doe';
  • The following UPDATE request makes the following updates to the college record of student Steven Smith:
    • school_name = 'UCLA'
    • GPA = 3.20
    • major = 'Computer Science'
           UPDATE student_record
            SET college.school.school_name = 'UCLA',
                college.school.GPA = 3.20,
                college.major = 'Computer Science'
           WHERE student.First_name() = 'Steven'
           AND   student.Last_name() = 'Smith';