Table Modification   «Prev  Next»
Lesson 5Updating an object
ObjectiveWrite SQL to update an object table.

Update an Object in Oracle

DML statements that update data within object tables are similar to statements used on relational tables. You must use table name aliases to update the records within an object table. In this lesson, we will look at the UPDATE statements for object tables whose attribute is associated with an object type.

Table aliases and dot notation

Let us look at the CUSTOMER_OBJ_TABLE table. Within this table, the FULL_ADDRESS attribute is associated with the ADDRESS_TYPE object type. We will update the attributes of the object type associated with the FULL_ADDRESS column. To update the record, we must use table aliases and dot notation to traverse to the attribute of the object type. One example of such an UPDATE statement looks like the following:

UPDATE customer_obj_table cot
SET cot.full_address.zip = '09982'
WHERE cot.cust_id = 1;

In this example, we update the attribute of the associated object type by using dot notation.

The UPDATE statement

To update a simple attribute of an object table, you can use a simple UPDATE statement. For example, to change PRODUCT_NAME within the PRODUCT_OBJ_TABLE table for a particular product, one example of the UPDATE statement looks like this:

UPDATE product_obj_table
SET product_name = 'Dog Food for Pups'
WHERE product_id = 4;

In this example, neither table alias or dot notation is used because the column that is updated is a simple column, not an attribute within an associated object type. Take a look at the MouseOver below for the syntax and an example of updating records within object tables:
  1. The UPDATE statement specifying the table
  2. SET table_alias.object_type_name.attribute_name =
  3. The WHERE clause
UPDATE <table_name> <table alias>
SET table_alias.object_type_name.attribute_name =
  <variable>
WHERE <clause>;

Update Statement Syntax
The following MouseOver provides an example of updating a record with an object table:
  1. The UPDATE statement for the CUSTOMER_OBJ_TABLE table
  2. Traversing down to the PO_BOX_ADDRESS attribute within the ADDRESS_TYPE object type within the object table
  3. WHERE customer_id = 1

UPDATE customer_obj_table cot
SET cot.full_address.po_box_address ='9982'
WHERE cot.cust_id = 1;

Updating Rows Example
In the next lesson, you will write DML statements for updating records by using a sub query.

Oracle DML Statements

Now that you have learned the techniques to update the rows within object tables, apply them to the following evaluative simulation by clicking the link below.
Oracle DML Statements