Teradata UPDATE Statement

The Teradata UPDATE statement is used to update records in an existing Teradata table. Update of records can be done on one or more Teradata columns using a single UPDATE statement.

Teradata UPDATE Statement syntax

The generic syntax for updating all the columns in a table is as below.

UPDATE database_name.table_name
SET column_name = value;

For updating specific records WHERE condition needs to be added as follows.

UPDATE database_name.table_name
SET column_name = value
WHERE condition;

You can update multiple columns in a single update statement as well. The syntax for doing the same as below.

UPDATE database_name.table_name
SET column1 = value1,
column2 = value2,
...
columnN = valueN
WHERE condition;

Parameters Explanation

Parameter Explanation
database_name The name of the owner database for the table.
table_name The name of the table from where you want to delete all the records.
where condition The condition for deleting specific row(s).
column_name The name of the column which you want to update.
value The new value needs to be updated.

Teradata UPDATE Statement example

Consider the following Employee table.

Teradata Update Statement

1) Updating Specific Records

The below statement updates the designation TRAINEE to BUSINESS ANALYST.

UPDATE TUTORIALSBOOK.Employee
SET DESIGNATION='BUSINESS ANALYST'
WHERE DESIGNATION='TRAINEE';

*** Update completed. One row changed.
*** Total elapsed time was 1 second.

We can verify the update rows in the Employee table using the select statement.

Teradata update statement

2) Updating Multiple Columns

The following statement updates multiple columns in a single statement.

UPDATE TUTORIALSBOOK.Employee
SET DESIGNATION='ASSISTANT MANAGER',
SALARY=50000
WHERE DESIGNATION='BUSINESS ANALYST';

*** Update completed. 2 rows changed.
*** Total elapsed time was 1 second.

Let’s check the Employee table to check the changes.

 *** Query completed. 8 rows found. 7 columns returned.
 *** Total elapsed time was 1 second.

      EMPNO  FIRST_NAME                 LAST_NAME                    HIREDATE  DESIGNATION               SALARY       DEPTNO
-----------  -------------------------  -------------------------  ----------  --------------------  ----------  -----------
       1001  STEFAN                     SALVATORE                  2001-04-12  ASSISTANT MANAGER       50000.00           40
       1002  DIANA                      LORANCE                    2005-01-05  TECHNICAL ARCHITECT     75000.00           10
       1003  JAMES                      MADISON                    2005-09-18  MANAGER                 60000.00           20
       1004  JONES                      NICK                       2004-08-19  HR ANALYST              35000.00           30
       1005  LUCY                       GILLER                     2006-04-01  HR ASSOCIATE            30000.00           30
       1006  ISSAC                      STEFAN                     2002-11-18  ASSISTANT MANAGER       50000.00           10
       1007  NANCY                      GILBERT                    2003-04-06  CLERK                   18000.00           10
       1008  JOHN                       SMITH                      2005-10-12  SALESMAN                20000.00           10

3) Updating all records in a column

The below statement updates all the records of the DEPTNO column to 50.

UPDATE TUTORIALSBOOK.Employee
SET DEPTNO=50;

*** Update completed. 8 rows changed.
*** Total elapsed time was 1 second.

Finally check the Employee table to see the changes.

 *** Query completed. 8 rows found. 7 columns returned.
 *** Total elapsed time was 1 second.

      EMPNO  FIRST_NAME                 LAST_NAME                    HIREDATE  DESIGNATION               SALARY       DEPTNO
-----------  -------------------------  -------------------------  ----------  --------------------  ----------  -----------
       1001  STEFAN                     SALVATORE                  2001-04-12  ASSISTANT MANAGER       50000.00           50
       1002  DIANA                      LORANCE                    2005-01-05  TECHNICAL ARCHITECT     75000.00           50
       1003  JAMES                      MADISON                    2005-09-18  MANAGER                 60000.00           50
       1004  JONES                      NICK                       2004-08-19  HR ANALYST              35000.00           50
       1005  LUCY                       GILLER                     2006-04-01  HR ASSOCIATE            30000.00           50
       1006  ISSAC                      STEFAN                     2002-11-18  ASSISTANT MANAGER       50000.00           50
       1007  NANCY                      GILBERT                    2003-04-06  CLERK                   18000.00           50
       1008  JOHN                       SMITH                      2005-10-12  SALESMAN                20000.00           50

Please get connected & share!

Advertisement