Rules for Using Scalar Subqueries in a DELETE Statement
You can specify a scalar subquery in the WHERE clause of a DELETE statement in the same way you can specify one for a SELECT statement.
You can also specify a DELETE statement with a scalar subquery in the body of a trigger. However, Teradata Database processes any noncorrelated scalar subquery you specify in the WHERE clause of a DELETE statement in a row trigger as a single‑column single‑row spool instead of as a parameterized value.