Examples - Teradata Database

Example: UPDATE to Set a Specific Value

The following request updates the yrsexp column in the employee table for employee Greene, who is employee number 10017:

     UPDATE employee
     SET yrs_exp = 16
     WHERE empno = 10017;

Example: UPDATE by a Percentage

The following request updates the employee table to apply a 10 percent cost of living increase to the salary for all employees:

     UPDATE employee
     SET salary = salary * 1.1 ALL ;

Example: UPDATE to Set a Null Value

The following request places a null in the salary column for employee number 10001:

     UPDATE employee
     SET salary = NULL
     WHERE emp_no = 10001 ;

Example: Updating a Nonpartitioned Column with an Equality Constraint

This example shows an update of a nonpartitioned column with an equality constraint on the partitioning column. With partition level locking, an all-AMP PartitionRange lock is used. The partition list contains a single partition pair.

The table definition for this example is as follows:

  CREATE TABLE HLSDS.SLPPIT1 (PI INT, PC INT, X INT, Y INT)
      PRIMARY INDEX (PI)
      PARTITION BY (RANGE_N(PC BETWEEN 1 AND 10 EACH 1));

An EXPLAIN of the UPDATE statement shows the partition lock:

  Explain UPDATE HLSDS.SLPPIT1 SET X = 3 WHERE PC = 10;
  1) First, we lock HLSDS.SLPPIT1 for write on a reserved rowHash in 
      a single partition to prevent global deadlock.
  2) Next, we lock HLSDS.SLPPIT1 for write on a single partition.
  3) We do an all-AMPs UPDATE from a single partition of HLSDS.SLPPIT1
      with a condition of ("HLSDS.SLPPIT1.PC = 10") with a residual
      condition of ("HLSDS.SLPPIT1.PC = 10").  The size is estimated
      with no confidence to be 1 row.  The estimated time for this 
      step is 0.05 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
      in processing the request.

Example: UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause

Because the following UPDATE request specifies the correlation name e for employee in its FROM clause, you must specify e in place of employee immediately following the UPDATE keyword.

     UPDATE e
     FROM employee AS e, department AS d
     SET salary = salary * 1.05
     WHERE e.emp_no = d.emp_no;

Example: UPDATE With a Subquery in its WHERE Clause

The following examples perform the same UPDATE operation using either a subquery join with a derived table or a simple equality join on column_1 in table_1 and table_2.

     UPDATE table_1
     SET column_1 = 1
     WHERE column_1 IN (SELECT column_2
                        FROM table_2);

     UPDATE table_1
     SET column_1 = 1
     WHERE table_1.column_1=table_2.column_1;

Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause

The following UPDATE operation uses a subquery to decrease the list price on an electroacoustic CD budget line for labels containing the string ‘erstwhile’:

     UPDATE disc
     SET price = price * .85
     WHERE label_no IN (SELECT label_no
                        FROM label
                        WHERE label_name LIKE '%erstwhile%')
                        AND   line = 'NEA';

You can obtain the same result by writing the query using a join between the disc and label tables on label_no:

     UPDATE disc
     SET price = price * .85
     WHERE disc.label_no = label.label_no
     AND   label_name LIKE '%erstwhile%'
     AND   line = 'NEA';

Example: UPDATE With a Scalar Noncorrelated Subquery in its SET Clause

You can specify a scalar subquery in the same way that you specify a column or constant in the SET clause of an UPDATE request.

The following example specifies a scalar subquery in its SET clause:

     UPDATE sales_sum_table AS sst
     SET  total_sales = (SELECT SUM(amount)
                         FROM sales_table AS s
                         WHERE s.day_of_sale BETWEEN sst.period_start 
                                             AND     sst.period_end);

Example: UPDATE With a Scalar Correlated Subquery in its SET Clause

When a scalar correlated subquery is specified in the SET clause, even if the UPDATE request does not have a FROM clause, Teradata Database treats the update as a joined update because of the scalar correlated subquery.

The following example requires salesumtable to be joined with salestable, and the update is done using a merge update operation via a spool.

     UPDATE sales_sum_table AS sst
     SET  total_sales = (SELECT SUM(amount) 
                         FROM sales_table AS s
                         WHERE s.day_of_sale BETWEEN sst.period_start 
                                             AND     sst.period_end);

Example: UPDATE With a Correlated Subquery in its WHERE Clause

The following correlated subquery is executed once for each row of the outer reference, publisher, and since two publishers have no books in the library two rows of publisher are modified.

     UPDATE publisher
     SET pub_num = NULL
     WHERE 0 = (SELECT COUNT(*)
                FROM book
                WHERE book.pub_num = publisher.pub_num);

Two publishers have books in the library, and two publishers do not.

For additional examples of correlated and noncorrelated subqueries, see Correlated Subqueries.

Example: UPDATE With a Noncorrelated Subquery in its WHERE Clause

For a noncorrelated subquery, change the subquery to include all tables it references in the inner FROM clause.

     UPDATE publisher
     SET pubnum = NULL
     WHERE 0 = (SELECT COUNT(*)
                FROM book, publisher
                WHERE book.pubnum = publisher.pubnum);

The request does not contain a correlated subquery and the condition in the subquery has a local defining reference. The count, determined once, is nonzero, and no rows are deleted.

For additional examples of correlated and noncorrelated subqueries, see Correlated Subqueries.

Example: UPDATE With a Join

The following example updates the employee table to give each employee a 5 percent salary increase using a join between the employee and department tables.

     UPDATE employee
     FROM department AS d
     SET salary = salary * 1.05
     WHERE employee.dept_no = d.dept_no
     AND   salary_pool > 25000;

Example: UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses

Assume you have created an SQL UDF named value_expression. The following example updates table t1 using the result of value_expression when it is passed values for columns a1 and b1 to update columns a1 and b1 for rows where the invocation of value_expression using the values for columns c1 and d1 are greater than 5.

     UPDATE t1
     SET b1 = test.value_expression(t1.a1, t1.b1)
     WHERE test.value_expression(t1.c1, t1.d1) > 5;

Example: UPDATE With a Mutator SET Clause

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

Example: Update Requests Using the DEFAULT Function

You can specify a DEFAULT function without a column name as the expression in the SET clause.

The column name for the DEFAULT function is the column specified as the column name. The DEFAULT function evaluates to the default value of the column specified as column_name.

The examples below assume the following table definition:

     CREATE TABLE table_11 (
       col_1 INTEGER,
       col_2 INTEGER DEFAULT 10,
       col_3 INTEGER DEFAULT 20,
       col_4 CHARACTER(60));

The following UPDATE requests are valid:

The following example updates the values of col3 to 20 (its default value) for all rows:

     UPDATE table_11
       SET col_3 = DEFAULT;

The following example updates the values of Col3 to 20 (its default value) for rows where the value of col1=5.

     UPDATE table_11
       SET col_3 = DEFAULT
         WHERE Col1 = 5;

Assume the following table definition for the next example:

     CREATE TABLE table_12 (
       x INTEGER,
       y INTEGER);

The following example updates the values of col3 to 20 (its default value) depending on whether the WHERE condition evaluates to true or not.

     UPDATE table_11
       SET col_3 = DEFAULT
         WHERE 5 < ANY
          (SELECT y
           FROM table_12);

You can specify a DEFAULT function with a column name in the source expression. The DEFAULT function evaluates to the default value of the column name specified as the input argument to the DEFAULT function. For example, DEFAULT(col_2) evaluates to the default value of col_2. This is a Teradata extension.

The following UPDATE request is valid. The input argument to the DEFAULT function is col_2. Therefore, the DEFAULT function evaluates to the default value of the col_2 and then set col_3 to this value. Specifically, it updates the values of col_3 to 10 (the default value of col_2) for all rows.

     UPDATE table_11
       SET col3 = DEFAULT(col_2);

The following example updates the values of col_3 to 10 (the default value of col_2) for rows where the value of col_1 is 5.

     UPDATE table_11
        SET col_3 = DEFAULT(col_2)
         WHERE col_1 = 5;

You can specify a DEFAULT function with a column name anywhere in the update expression. This is a Teradata extension to the ANSI SQL:2011 standard.

The following UPDATE request is valid. The input argument to the DEFAULT function is col_2; therefore, the DEFAULT function evaluates to the default value of col_2. The request then updates the value of col_3 to 15 (10+5, the default value of col_2 + 5) for all rows.

     UPDATE table_11
       SET col_3 = DEFAULT(col_2) + 5;

The following example updates the value of col3 to 15 (the default value of col2+5) for all rows.

     UPDATE table_11
       SET col_3 = DEFAULT(col_2) + 5 ALL;

The following example updates the values of col3 to 15 (the default value of col_2 + 5) for rows where the value of col_1=20.

     UPDATE table_11
       SET col_3 = DEFAULT(col_2)+5
         WHERE col_1 = 20;

When there is no explicit default value associated with the column, the DEFAULT function evaluates to null.

Assume the following table definition for the examples that follow:

     CREATE TABLE table_13 (
       col_1 INTEGER,
       col_2 INTEGER NOT NULL,
       col_3 INTEGER NOT NULL DEFAULT NULL,
       col_4 INTEGER CHECK (col_4>100) DEFAULT 99 );

In the following example, col_1 is nullable and does not have an explicit default value associated with it; therefore, the DEFAULT function evaluates to null.

     UPDATE table_13
       SET col_1 = DEFAULT;

The following UPDATE requests are equivalent. For both requests, the DEFAULT function evaluates to the default value of col_3 for rows where the value of col_1 is 5.

     UPDATE table_11
       SET col_3 = DEFAULT(c3)
         WHERE col_1 = 5;
     UPDATE table_11
       SET col_3 = DEFAULT
         WHERE col_1 = 5;

Example: UPDATE Using a PERIOD Value Constructor

The following example uses tables t1 and t2, which are defined as follows:

     CREATE TABLE t1 (
       c1 INTEGER
       c2 PERIOD(DATE))
     UNIQUE PRIMARY INDEX (c1);

     CREATE TABLE t2 (
       a INTEGER
       b DATE
       c DATE)
     UNIQUE PRIMARY INDEX (a);

The following two UPDATE requests both use a PERIOD value constructor:

     UPDATE t1
     SET c2 = PERIOD(DATE '2007-02-03', DATE '2008-02-04'));
     UPDATE t1 FROM t2
     SET c2 = PERIOD(b,c)
     WHERE t2.a = 2;

Example: Updating a NoPI Table

The following UPDATE request updates the NoPI table nopi012_t1 aliased as t1.

     UPDATE t1
     FROM nopi012_t1 AS t1, nopi012_t2 AS t2
     SET c3 = t1.c3 * 1.05
     WHERE t1.c2 = t2.c2;

Example: Updating a Table with an Implicit Isolated Load Operation

For information on defining a load isolated table, see the WITH ISOLATED LOADING option for CREATE TABLE and ALTER TABLE in SQL Data Definition Language - Syntax and Examples.

Following is the table definition for the example.

CREATE TABLE ldi_table1,
     WITH CONCURRENT ISOLATED LOADING FOR ALL
     (a INTEGER,
      b INTEGER,
      c INTEGER)
PRIMARY INDEX ( a );

This statement performs an update on the load isolated tableldi_table1 as an implicit concurrent load isolated operation:

UPDATE WITH CONCURRENT ISOLATED LOADING ldi_table1 SET b = b + 1;

An EXPLAIN shows that the UPDATE step is performed as concurrent load isolated. The isolated load begins and performs a concurrent load isolated UPDATE of ldi_table1 (Load Uncommitted) with a condition of ("(ldi_table1.TD_ROWLOADID_DEL = 0) AND ((1=1))").

Example: Updating a Table with an Explicit Isolated Load Operation

For information on defining a load isolated table and performing an explicit isolated load operation, see the WITH ISOLATED LOADING option for CREATE TABLE and ALTER TABLE, in addition to the Load Isolation Statements chapter in SQL Data Definition Language - Syntax and Examples.

Following is the table definition for the example:

CREATE TABLE ldi_table1,
     WITH CONCURRENT ISOLATED LOADING FOR ALL
     (a INTEGER,
      b INTEGER,
      c INTEGER)
PRIMARY INDEX ( a );

This statement starts an explicit concurrent load isolated operation on table ldi_table1:

 BEGIN ISOLATED LOADING ON ldi_table1 USING QUERY_BAND 'LDILoadGroup=Load1;';

This statement sets the session as an isolated load session:

SET QUERY_BAND='LDILoadGroup=Load1;' FOR SESSION;

This statement performs an explicit concurrent load isolated update on table ldi_table1:

 UPDATE ldi_table1 SET c = c + 1;

This statement ends the explicit concurrent load isolated operation:

END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=Load1;';

You can use this statement to clear the query band for the next load operation in the same session:

SET QUERY_BAND = 'LDILoadGroup=NONE;' FOR SESSION;

Example: Application of Row-Level Security SELECT and UPDATE Constraints When User Lacks Required Privileges (UPDATE Request)

This example shows how the SELECT and UPDATE constraints are applied when a user that does not have the required privileges submits an UPDATE request in an attempt to update the classification level value for a row. The SELECT constraints filter out the rows that the user is not permitted to access and the UDPATE constraints restrict the user from executing the update operation on the target row.

The classification level value is stored in the classification_level column, one of the constraint columns. The other constraint column is classification_categories.

The statement used to create the table in this example is:

     CREATE TABLE rls_tbl(        col1 INT,        col2 INT,         classification_levels   CONSTRAINT,        classification_categories CONSTRAINT);

The user’s sessions constraint values are:

     Constraint1Name LEVELS
     Constraint1Value 2
     Constraint3Name CATEGORIES
     Constraint3Value '90000000'xb

Following is the UPDATE statement:

      UPDATE rls_tbl SET col1=2 where col1=1;

The EXPLAIN shows the outcome of the SELECT and UPDATE constraints. A RETRIEVE step on RS.rls_tbl is performed by way of the primary index "RS.rls_tbl.col1 = 1" with a residual condition of ("((SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels ))= 'T') AND ((SYSLIB.SELECTCATEGORIES ('90000000'XB, RS.rls_tbl.categories ))='T')").

Next, there is a MERGE DELETE of the results to RS.rls_tbl with the updated rows constrained by (RS.rls_tbl.levels = SYSLIB.UPDATELEVEL (2, {LeftTable}.levels)), (RS.rls_tbl.categories = SYSLIB.UPDATECATEGORIES ('90000000'XB, {LeftTable}.categories)).

Then, there is a MERGE into RS.rls_tbl of the results.