| Lesson 7 | Deleting from an object table |
| Objective | Write 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);
Delete Records
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:
Deleting Object Table - Exercise