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.

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: In this example, we update the attribute of the associated object type by using dot notation.

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

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 diagram below for the syntax and an example of updating records within object tables:

Location 1	The UPDATE statement specifying the table
Location 2	Traversing down to the attribute within the object type associated with the object table
Location 3	The WHERE clause

An UPDATE statement modifies existing data, following the same "transaction control rules" as INSERT. The syntax for UPDATE is

UPDATE table_name
SET column_name = select_statement | value [, column_name = value]
[WHERE where_clause | WHERE CURRENT OF cursor];

The table_name can be any table, synonym, or updatable view. Column_name is any column in the table_name specified. The SET clause can include more than one column_name in a comma-delimited list. Columns can be set equal to an integer, a variable, or any valid expression. They can also be set equal to the result of a subselect. The optional WHERE CURRENT OF clause is useful when working with a cursor that is declared with a FOR UPDATE clause. The where_clause can be any column in the table compared to any expression. The WHERE CURRENT OF clause works with UPDATEs and DELETEs, and says to operate against the current record from the cursor. The first example performs an update against a table, with its value derived from a variable of the same type as the column.

--Available online as part of Update.sql
SET SERVEROUTPUT ON
DECLARE
v_num_pages BOOKS.NUM_PAGES%TYPE;
v_isbn BOOKS.ISBN%TYPE := '72230665';
BEGIN
SELECT num_pages
INTO v_num_pages
FROM books
WHERE isbn = v_isbn;
DBMS_OUTPUT.PUT_LINE('Number of pages before: '||v_num_pages);
v_num_pages := v_num_pages + 200;
UPDATE books
SET num_pages = v_num_pages
WHERE isbn = v_isbn;
DBMS_OUTPUT.PUT_LINE('Number of pages after: '||v_num_pages);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;
/

The following diagram provides an example of updating a record with an object table:

Location 1	The UPDATE statement for the CUSTOMER_OBJ_TABLE table
Location 2	Traversing down to the PO_BOX_ADDRESS attribute within the ADDRESS_TYPE object type within the object table
Location 3	The WHERE clause [WHERE customer_id = 1]

The UPDATE statement lets you update one or more column values in one row or a set of rows in a table. It supports different direct assignments to each column value by using bind variables, literal values, and subqueries. The WHERE clause in the UPDATE statement qualifies which rows are changed by the UPDATE statement.
Note: All rows in the table are updated when you run an UPDATE statement without a WHERE clause. Update anomalies occur much like the insertion anomalies that happen when you insert two rows with the same information. The only difference is that the UPDATE statement alters a second row when it should not. You eliminate updating multiple rows in error by using unique indexes across sets of columns to prevent it, as described in the preceding section, INSERT Statements. The UPDATE statement has the following prototype:

UPDATE table_name [alias]
SET column1 = {value | function_call | select_statement}
, column2 = {value | function_call | select_statement}
, column(n+1) = {value | function_call | select_statement}
WHERE list_of_comparative_operations
[RETURNING column_name INTO :bind_variable];

You can use a literal value, a bind variable, a locally scoped variable, a function return value, or a scalar subquery when you set a new value in an UPDATE statement. The assigned values must match the column datatype. You should note that unlike when using the alias assignment in the SELECT clause, you must exclude the AS clause or you raise an ORA-00971 error that says you are missing the SET clause. The RETURNING INTO clause is used to shift a column value reference for an Oracle LOB datatype into a bind variable, as demonstrated in Chapter 8, Appendix C, and Appendix F. Appendix C demonstrates uploading a large string by using the UploadBioSQL.php program. A sample UPDATE statement using a correlated subquery updates the middle initial for a single row in the CONTACT table as follows:

UPDATE contact c1
SET c1.middle_initial = 'B'
WHERE EXISTS (SELECT NULL
FROM contact c2
WHERE c1.contact_id = c2.contact_id
AND c2.last_name = 'Vizquel'
AND c2.first_name = 'Oscar');

The correlated query could have been eliminated by putting the LAST_NAME and FIRST_NAME column value comparisons in the WHERE clause. This illustrates that there are many ways to do equivalent things using SQL statements.

We have learned the techniques to update the rows within object tables, and the following section discusses more with respect to how to write Oracle DML statements. To help you truly understand how all the disparate pieces of the Oracle Database work together, this page contains an example of the steps taken by the Oracle Database to respond to a user request. This example examines the work of a user who is adding new information to the database, in other words, executing a transaction. A transaction is a work request from a client to retrieve, insert, update, or delete data. We will focus on transactions that modify data, as opposed to retrieving data. The statements that change data are a subset of the SQL language called Data Manipulation Language. Transactions must be handled in a way that guarantees their referential integrity. I will discuss a few basic concepts relating to transactions now in order to explain the example in this section:

In database terms, a transaction is a logical unit of work composed of one or more data changes. A transaction may consist of multiple

1. INSERT,
2. UPDATE, and/or
3. DELETE statements

affecting data in multiple tables. The entire set of changes must succeed or fail as a complete unit of work. A transaction starts with the first DML statement and ends with either a 1) commit or 2) a rollback. Oracle also supports autonomous transactions, which are transactions whose work is committed or rolled back, but that exist within the context of a larger transaction. Autonomous transactions are important because they can commit work without destroying the context of the larger transaction.

UPDATE customer_obj_table cot
set cot.full_address.state = ‘FL’
WHERE cust_id = 26

select * from CANINE:
NAME
-------
TRAUBLE
insert into CANINE values ('SPECS');
insert into CANINE values ('BLACKIE');
insert into CANINE values ('FRED');
insert into CANINE values ('MISTY');
update CANINE set Name = 'TROUBLE' where Name = 'TRAUBLE';
rollback;
select * from CANINE:

NAME
--------
TRAUBLE
update CANINE set Name = 'TROUBLE' where Name = 'TRAUBLE';
insert into CANINE values ('SPECS');
insert into CANINE values ('BLACKIE');
insert into CANINE values ('FRED');
commit;
insert into CANINE values ('MISTY');
rollback;
select * from CANINE:
NAME
-------
TROUBLE
SPECS
BLACKIE
FRED

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