Table Modification   «Prev  Next»

Lesson 6Updating with a sub query
ObjectiveWrite SQL to execute Update using a Subquery.

Write SQL Sub Query to Update Table

DML statements for object tables that have references to other objects require the use of a sub query within the UPDATE statement. In the case of object tables with either associated object types or references to other objects, table aliases must be used within the UPDATE statements.
For example, within the PRODUCT_OBJ_TABLE table, PACKAGE_ID refers to the PRODUCT_TYPE object type. To update this column, you must first perform a query to retrieve the reference value. An example of such an UPDATE statement looks like this: 

UPDATE product_obj_table p1
SET package_id = (SELECT REF(p2) FROM product_obj_table
  p2 WHERE product_id = 4)
WHERE product_id = 36;

Take a look at the MouseOver below for the syntax and an example of updating records within object tables:
Use SQL UPDATE
  1. The UPDATE statement specifying the table
  2. SET attribute name equal to sub query
  3. From table name
  4. where clause for the sub query to retrieve the reference information
  5. WHERE clause for the update statement

SQL Update Query Syntax
An example of using a sub query to update a record is illustrated by the following MouseOver:
Execute update with Query 
UPDATE product_obj_table p1
SET package_id = (SELECT REF(p2) 
FROM product_obj_table p2
  WHERE product_id = 14)
WHERE product_id = 22;
  1. The UPDATE statement
  2. SET package_id =SELECT
  3. FROM product_obj_table
  4. WHERE product_id =14
  5. WHERE product_id=22

SQL Update QuerySyntax
In the next lesson, you will learn to write DML statements to delete records from object tables.