Example: DELETE Set of Rows
This example deletes all rows with a department number of 500 from the employee table:
DELETE FROM employee WHERE deptno=500;
Example: DELETE All Rows
This example deletes all rows from the employee table (the ALL keyword may be omitted):
DELETE FROM employee ALL;
Example: DELETE Single Row
This example deletes the row for employee 10011 from the employee table:
DELETE FROM employee WHERE empno=10011;
Example: Join Condition DELETE
This example deletes rows from the employee table for employees who work in NYC. This operation joins the employee and department tables in the WHERE clause predicate:
DELETE FROM employee WHERE employee.deptno = department.deptno AND department.location = 'NYC';
Example: Self-Join Condition DELETE
This example deletes rows from the employee table for managers who have less work experience than their employees. This delete performs a self-join on the employee table:
DELETE FROM employee AS managers
WHERE managers.deptno = employee.deptno
AND managers.jobtitle IN ('Manager', 'Vice Pres')
AND employee.yrsexp > managers.yrsexp;
Example: Delete Rows with an Equality Constraint on a Partitioning Column
This is an example of deleting rows with an equality constraint on partitioning column. With partition level locking, an all-AMPs partition range lock is placed. The partition range has 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 DELETE statement shows the partition lock:
Explain DELETE HLSDS.SLPPIT1 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 DELETE of a single partition from HLSDS.SLPPIT1
with a 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.03 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved in
processing the request.
Example: Delete Rows with an Indirect Constraint on the Target Table Partitioning Column
This delete operation uses an indirect constraint on target table partitioning column, that is slppit1.pc = srct1.b AND srct1.b = 10, to generate a single partition elimination list so a PartitionRange lock can be placed on table slppit1.
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));
CREATE TABLE HLSDS.SRCT1 (A INT, B INT, C INT) PRIMARY INDEX (A);
An EXPLAIN of the DELETE statement shows the partition lock:
Explain DELETE HLSDS.SLPPIT1 FROM HLSDS.SRCT1
WHERE SLPPIT1.PC = SRCT1.B AND SRCT1.B = 10;
1) First, we lock HLSDS.SRCT1 for read on a reserved rowHash to prevent
global deadlock.
2) Next, we lock HLSDS.SLPPIT1 for write on a reserved rowHash in a
single partition to prevent global deadlock, and we lock
HLSDS.SLPPIT1 for read on a reserved rowHash in a single partition
to prevent global deadlock.
3) We lock HLSDS.SRCT1 for read, we lock HLSDS.SLPPIT1 for write on a
single partition, and we lock HLSDS.SLPPIT1 for read on a single
partition.
4) We do an all-AMPs RETRIEVE step from HLSDS.SRCT1 by way of an
all-rows scan with a condition of ("(NOT (HLSDS.SRCT1.A IS NULL ))
AND (HLSDS.SRCT1.B = 10)") into Spool 2 (all_amps), which is
duplicated on all AMPs. The size of Spool 2 is estimated with no
confidence to be 4 rows (68 bytes). The estimated time for this
step is 0.07 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to a single partition of
HLSDS.SLPPIT1 with a condition of ("HLSDS.SLPPIT1.PC = 10") with
a residual condition of ("HLSDS.SLPPIT1.PC = 10"). Spool 2 and
HLSDS.SLPPIT1 are joined using a product join, with a join
condition of ("HLSDS.SLPPIT1.PC = B"). The result goes into
Spool 1 (all_amps), which is built locally on the AMPs. Then we
do a SORT to partition Spool 1 by rowkey and the sort key in spool
field1 eliminating duplicate rows. The size of Spool 1 is
estimated with no confidence to be 1 row (18 bytes). The estimated
time for this step is 0.06 seconds.
6) We do an all-AMPs MERGE DELETE to HLSDS.SLPPIT1 from Spool 1
(Last Use) via the row id. The size is estimated with no confidence
to be 1 row. The estimated time for this step is 0.81 seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved in
processing the request.
Example: Macro For DELETE
The following macro structures the SELECT and DELETE statements as a single multistatement request:
CREATE MACRO res_use (from_date (DATE, DEFAULT DATE), to_date (DATE, DEFAULT DATE), from_time (INTEGER, DEFAULT 0), to_time (INTEGER, DEFAULT 999999), proc (VARCHAR(4), DEFAULT 'P'), secs (SMALLINT, DEFAULT 600) ) AS (SELECT the_date (TITLE 'Resource//TheDate'), the_time (TITLE 'Utilized//TheTime'), proc, AVERAGE(hits) (TITLE 'Avg//Hits'), MAXIMUM(cpu) (TITLE 'Max//CPU'), AVERAGE(cpu) (TITLE 'Avg//CPU'), MAXIMUM(disk) (TITLE 'Max//Disk'), AVERAGE(disk) (TITLE 'Avg//Disk'), MAXIMUM(host) (TITLE 'Max//Host'), AVERAGE(host) (TITLE 'Avg//Host'), MAXIMUM(chan) (TITLE 'Max//Chan'), AVERAGE(chan) (TITLE 'Avg//Chan') FROM DBC.res_use_view GROUP BY the_date, the_time, proc WHERE the_date BETWEEN :from_date AND :to_date AND the_time BETWEEN :from_time AND :to_time AND proc CONTAINS :proc AND secs EQ :secs ORDER BY proc, the_date, the_time ;DELETE FROM res_use_view ALL;);
If the preceding macro is executed in Teradata session mode, but not within the boundaries of an explicit transaction, fastpath delete processing is used for the DELETE statement. See Unconstrained Fastpath Delete Processing.
In ANSI session mode, the system uses fastpath delete processing for the DELETE statement when a COMMIT is included in the macro or when a COMMIT is specified at the end of the request line, and immediately following the execution of the macro, within the request.
For example, the last line of the preceding macro would read as follows:
DELETE FROM res_use_view ALL ; COMMIT;) ;
The system uses slow path processing for the DELETE statement when no COMMIT is stated in the macro itself or within the request that executes the macro.
Example: DELETE ALL Multistatement Request
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;
Example: Join Condition DELETE With FROM Clause and Correlation Name
The following example deletes employees from the employee table using a join on the employee and department tables.
DELETE employee FROM department AS d, employee WHERE employee.dept_no = d.dept_no AND salary_pool < 50000;
Example: Join Condition DELETE With No FROM Clause
The following example uses different syntax to perform the same action as Example: Join Condition DELETE With FROM Clause and Correlation Name. Because this DELETE statement does not specify a FROM clause, you cannot use correlation names for the tables.
DELETE employee WHERE employee.dept_no = department.dept_no AND salary_pool < 50000;
Example: Join Condition DELETE With Derived Table Subquery
The following example uses a subquery to perform the same action as Example: Join Condition DELETE With FROM Clause and Correlation Name and Example: Join Condition DELETE With No FROM Clause.
DELETE FROM employee WHERE dept_no IN (SELECT dept_no FROM department WHERE salary_pool < 50000);
Example: UDT Support For DELETE Statements
The following examples show valid use of UDT expressions in a DELETE statement:
DELETE FROM test_table
WHERE circle_udt < NEW circle('1,1,9');
DELETE FROM test_table1, test_table2
WHERE test_table1.mycirc = test_table2.mycirc;
Example: DELETE and NoPI Tables
The following examples are based on this NoPI table definition:
CREATE TABLE new_sales, FALLBACK ( item_nbr INTEGER NOT NULL, sale_date DATE FORMAT 'MM/DD/YYYY' NOT NULL, item_count INTEGER) NO PRIMARY INDEX;
The following DELETE statement requires a full-table scan because new_sales has neither a primary nor a secondary index.
DELETE FROM new_sales WHERE item_nbr = 100;
The following DELETE statement uses fastpath delete processing if it is submitted in Teradata session mode as an implicit transaction.
DELETE FROM new_sales;
The following DELETE statement uses fastpath delete processing when it is submitted in Teradata session mode as a single request.
BEGIN TRANSACTION ;DELETE FROM new_sales ;END TRANSACTION;
Assume the following single-statement request is submitted in Teradata session mode.
BEGIN TRANSACTION;
Then the following DELETE statement uses fastpath delete processing when it is submitted with an END TRANSACTION statement in a multistatement request.
DELETE FROM new_sales ;END TRANSACTION;
The following DELETE statement uses fastpath delete processing when it is submitted with a COMMIT statement in ANSI mode in a multistatement request.
DELETE FROM new_sales ;COMMIT;
Example: Using an SQL UDF in a DELETE Request WHERE Condition
You can specify an SQL UDF in a WHERE clause search condition if it returns a value expression.
DELETE FROM t1 WHERE a1 = test.value_expression(b1, c1);
Example: Deleting from 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.
CREATE TABLE ldi_table1,
WITH CONCURRENT ISOLATED LOADING FOR ALL
(a INTEGER,
b INTEGER,
c INTEGER)
PRIMARY INDEX ( a );
DELETE WITH ISOLATED LOADING FROM ldi_table1 WHERE a > 10;
Example: Deleting from 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 );
BEGIN ISOLATED LOADING ON ldi_table1
USING QUERY_BAND 'LDILoadGroup=Load1;';
SET QUERY_BAND='LDILoadGroup=Load1;' FOR SESSION;
DELETE FROM ldi_table1 WHERE a > 10;
END ISOLATED LOADING FOR QUERY_BAND 'LDILoadGroup=Load1;';
SET QUERY_BAND = 'LDILoadGroup=NONE;' FOR SESSION;
Example: Deleting a Row From a Table With Row-Level Security Protection
In this example, assume that the user submitting the request has the privileges required to delete a row from inventory.
The following DELETE statement deletes a row from a table named inventory.
DELETE FROM inventory WHERE col_1 = 12126;
Example: Row-Level Security DELETE and SELECT Constraints For User Lacking Required Privileges (DELETE)
This example shows how the DELETE and SELECT constraints are applied when a user without the required OVERRIDE privileges attempts to execute a DELETE statement on a table that has the row-level security DELETE and SELECT constraints.
An EXPLAIN request modifier is used to show the steps involved in the execution of the request and the outcome of the application of the constraints.
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 session constraint values are:
Constraint1Name LEVELS Constraint1Value 2 Constraint3Name CATEGORIES Constraint3Value '90000000'xb
EXPLAIN Request Modifier with a DELETE Statement
This EXPLAIN request modifier shows the steps performed to execute the DELETE statement and the outcome of the application of the DELETE and SELECT constraints.
EXPLAIN DELETE FROM rls_tbl WHERE col1=2;
The system returns this EXPLAIN text.
*** Help information returned. 10 rows.
*** Total elapsed time was 1 second.
Explanation
----------------------------------------------------------------------
1) First, we do a single-AMP DELETE from RS.rls_tbl by way of the
primary index "RS.rls_tbl.col1 = 2" with a residual condition of (
"((SYSLIB.DELETELEVEL (RS.rls_tbl.levels ))= 'T') AND
(((SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels ))= 'T') AND
(((SYSLIB.DELETECATEGORIES (RS.rls_tbl.categories ))= 'T') AND
((SYSLIB.SELECTCATEGORIES ('90000000'XB, RS.rls_tbl.categories ))=
'T')))"). The size is estimated with no confidence to be 2 rows.
The estimated time for this step is 0.01 seconds.
-> No rows are returned to the user as the result of statement 1.
The total estimated time is 0.01 seconds.