Table Modification   «Prev  Next»
Lesson 7Deleting from an object table
ObjectiveWrite SQL to Delete Rows from an Object Table.

Write SQL to Delete Rows from an Object Table

To delete records from an object table, the DML statements can be simple DELETE statements. Alternatively, they can be based on a query.
For example, to delete a single record from the PRODUCT_OBJ_TABLE table, use a DELETE statement as simple as this:

DELETE FROM product_obj_table
WHERE product_id = 34;

This simple DELETE statement deletes a record for PRODUCT_ID with a value of 34. To delete records based on a query, the query must be within the WHERE clause of the DELETE statement. This is an example of such a DELETE statement:
DELETE FROM product_obj_table
WHERE package_id = (SELECT REF(pot) 
  FROM product_obj_table pot
WHERE pot.product_id = 39);

Deleting multiple records from a table depends on the WHERE clause, which selects the records from the object table. Here is an example:
DELETE FROM sale_header
WHERE cust_ref = (SELECT REF(cot) 
FROM customer_obj_table cot
WHERE cot.cust_id = 26);

In this example, we delete the records from the SALE_HEADER table based on its CUST_REF, which is queried from the CUSTOMER_OBJ_TABLE table.
The following Slide Show offers guidelines and examples on variations in deleting records from object tables:

Delete Table Variations
In the next lesson, we will wrap up this module.

Deleting Object Table - Exercise

Click the Exercise link below to try your hand at writing INSERT and UPDATE statements.
Deleting Object Table - Exercise