Example: UPDATE With a Mutator SET Clause - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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