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.
Oracle 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:
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: