Correlated Queries
Learning Outcomes
- Learn how write a correlated
UPDATE
statement.
Supporting Materials
The following web pages may help you understand the SQL syntax in this tutorial:
Lesson Materials
There are three scenarios for adding columns to tables:
- How to write a correlated
UPDATE
statement.
How to write a correlated UPDATE
statement
A correlated query is a specialized subquery that contains a join to an outer query. An UPDATE
statement can contain a correlated in the SET
clause, which allows the UPDATE
statement to change multiple rows with potentially different values.
SQL> UPDATE rental_item ri 2 SET rental_item_type = 3 (SELECT cl.common_lookup_id 4 FROM common_lookup cl 5 WHERE cl.common_lookup_code = 6 (SELECT TO_CHAR(r.return_date - r.check_out_date) FROM rental r 7 FROM rental r 8 WHERE r.rental_id = ri.rental_id)); |
Line 1 designates the rental_item
table as the target of the UPDATE
statement, and line 1 assigns a ri
table alias to the rental_item
table. Line 2 assigns the result of a correlated subquery to the rental_item_type
column. Line 8 matches the rows to update in the rental_item
table with the rental
table based on matching the primary key value to the foreign key value. The rental_id
column is the surrogate and primary key column of the rental
table, while the rental_id
column in the rental_item
table is a foreign key column.