Teradata DELETE TABLE

Teradata DELETE TABLE statement removes all the records from the Teradata table if execute without any condition. This command only removes the records from the specified table, the table structure remains unchanged.

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 TABLE Syntax

Syntax without WHERE condition

The following syntax is used while deleting all the records from a specific table.

DELETE FROM database_name.table_name;

Here,

  • 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.

Syntax with WHERE condition

The following syntax is used for deleting some specific records from the table.

DELETE FROM database_name.table_name where condition;

Here,

  • where condition – The condition for deleting specific row(s).

Teradata DELETE TABLE example

To understand the concept of Teradata DELETE TABLE, we use the below two tables.

Table: student

select * from tutorialsbook.student;

 *** Query completed. 3 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

    roll_no first_name                last_name                        DOB
----------- ------------------------- ------------------------- ----------
          3 Ranjit                    Bera                      1990-12-09
          1 Sagar                     Sharma                    1990-12-04
          2 Shankar                   Kumar                     1995-06-05

Table: student_details

select * from tutorialsbook.student_details;

 *** Query completed. 3 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

    roll_no first_name                last_name                        DOB
----------- ------------------------- ------------------------- ----------
          3 Ranjit                    Bera                      1990-12-09
          1 Sagar                     Sharma                    1990-12-04
          2 Shankar                   Kumar                     1995-06-05

1) Deleting all records

The following statement deletes all the records from the student table. As you can notice here WHERE condition is not specified.

DELETE FROM tutorialsbook.student;

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

You can verify the records of the table using the SELECT statement.

select * from tutorialsbook.student;

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

2) Deleting specific record(s)

The following example deletes only the specific record(s) which meet the criteria. Here, we have used the WHERE conditions to filter the records for deleting.

DELETE FROM tutorialsbook.student_details WHERE roll_no=3;

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

You can verify the records of the table using the SELECT statement.

*** Query completed. 2 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

    roll_no first_name                last_name                        DOB
----------- ------------------------- ------------------------- ----------
          1 Sagar                     Sharma                    1990-12-04
          2 Shankar                   Kumar                     1995-06-05

As you can see that only a specific record is deleted and the other two records are still present in the student_details table.

Please get connected & share!

Advertisement