Teradata DELETE Statement

Teradata DELETE statement deletes records from the Teradata table. With the help of the delete command, either the entire data or the specific record can be deleted.

You require DELETE privileges on the table for executing the DELETE statement and if you using execute the DELETE statement with the WHERE condition then you required SELECT privileges on all tables and views through which they are referenced.

Teradata Delete Statement syntax

The syntax for deleting all the records is as below.

DELETE FROM database_name.table_name;

The syntax for deleting specific records is as follows.

DELETE FROM database_name.table_name 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).

Teradata Delete Statement example

To understand the concept of the Teradata DELETE statement, we will use the below Employee table.

Definition of Employee table

SHOW TABLE TUTORIALSBOOK.Employee;

CREATE SET TABLE TUTORIALSBOOK.Employee ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      EMPNO INTEGER NOT NULL,
      FIRST_NAME CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      LAST_NAME CHAR(25) CHARACTER SET LATIN NOT CASESPECIFIC,
      HIREDATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      DESIGNATION VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      SALARY DECIMAL(8,2),
      DEPTNO INTEGER)
UNIQUE PRIMARY INDEX ( EMPNO );

Table: Employee

       EMPNO  FIRST_NAME                 LAST_NAME                    HIREDATE  DESIGNATION               SALARY       DEPTNO
-----------  -------------------------  -------------------------  ----------  --------------------  ----------  -----------
       1001  STEFAN                     SALVATORE                  2001-04-12  BUSINESS ANALYST        55000.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  TRAINEE                 22000.00           10
       1007  NANCY                      GILBERT                    2003-04-06  CLERK                   18000.00           10
       1008  JOHN                       SMITH                      2005-10-12  SALESMAN                20000.00           10

1) Delete specific records from a table

The below example shows how to delete specific records from a table.

DELETE FROM TUTORIALSBOOK.Employee
WHERE DESIGNATION='SALESMAN';

The above statement deletes records of the employee whose DESIGNATION is SALESMAN.

DELETE FROM TUTORIALSBOOK.Employee
WHERE DESIGNATION='SALESMAN';

*** Delete completed. One row removed.
*** Total elapsed time was 1 second.

The same can be verified using the select statement using with where condition.

SELECT * FROM TUTORIALSBOOK.Employee
WHERE DESIGNATION='SALESMAN';

*** Query completed. No rows found.
*** Total elapsed time was 1 second.

2) Delete all records from a table

The below statement deletes all the records from the table Employee. However, the table structure will not be deleted.

DELETE FROM TUTORIALSBOOK.Employee;

*** Delete completed. 7 rows removed.
*** Total elapsed time was 1 second.

The same can be verified using the select statement.

SELECT * FROM TUTORIALSBOOK.Employee;

*** Query completed. No rows found.
*** Total elapsed time was 1 second.

Please get connected & share!

Advertisement