DELETE
TDV supports the regular SQL DELETE statement.
Syntax
DELETE FROM <table>
[WHERE <criteria>]
Remarks
• The WHERE clause can have a subquery.
• All database objects referenced in the subquery must be from the same data source as the target of the DELETE.
• IN subqueries can be scalar or not.
• Depending on the relational operator, quantified subqueries may need to be scalar.
• If the subquery references incorrect rows, unexpected target rows might be affected.
• If the underlying data source has the truncate_table capability set, then the hints use_truncate and try_truncate can be used with the DELETE keyword.
Example (Deleting All Rows)
The following example deletes all the rows in the orders table:
DELETE FROM /shared/examples/ds_orders/orders
Example (Deleting Specific Rows)
The following example deletes the row where the product ID is 44 in the orders table:
DELETE FROM /shared/examples/ds_orders/orders
WHERE ProductID = 44
Example (Using a Subquery)
The following example uses a subquery:
DELETE FROM /shared/examples/ds_orders/orders
WHERE ProductID IN (SELECT ProductID FROM /shared/examples/ds_orders2/orderdetails)
Example (Using hints for Truncate)
The following example uses a subquery:
DELETE {option use_truncate} FROM /shared/examples/ds_orders/orders
In this case, the query engine will run TRUNCATE TABLE, if the truncate capability is set for the data source in the capabilities file. If not, an error will be displayed.
DELETE {option try_truncate} FROM /shared/examples/ds_orders/orders
In this case, the query engine will run TRUNCATE TABLE, if the truncate capability is set for the data source in the capabilities file. If not, DELETE statement will be executed.