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” on page 324.
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 10: Join Condition DELETE With FROM Clause and Correlation Name” on page 336. 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 10: Join Condition DELETE With FROM Clause and Correlation Name” on page 336 and “Example 11: Join Condition DELETE With No FROM Clause” on page 336.
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 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 : Application of Row-Level Security DELETE and SELECT Constraints When User Lacks 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.