17.05 - Example: DELETE ALL Multistatement Request - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The DELETE … ALL statement in the following BTEQ request invokes fastpath delete processing. The statements are combined into a multistatement request and are processed as an implicit transaction in Teradata session mode. Note that DELETE is the last statement in the request:

     SELECT *
     FROM DBC.log_on_off_v
     WHERE log_date = DATE
     AND   user_name = 'Administrator'
    ;SELECT log_time, user_name, event, account_name
     FROM DBC.log_on_off_v
     WHERE log_date = DATE
     AND   user_name NOT IN ('Administrator', 'SecAdmin', 'Oper')
    ;SELECT log_time, event, logical_host_id, pe_no
     FROM DBC.log_on_off_v
     WHERE log_date = DATE
     AND user_name = 'Oper'
    ;DELETE FROM DBC.log_on_off_v ALL ;

In ANSI session mode, the COMMIT statement must follow the DELETE request, so the last line of the above example would read:

     DELETE FROM DBC.log_on_off_v ALL ;
     COMMIT;