Updating Rows Example using PL/SQL

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

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

UPDATE Statements

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.