Database Tutorial

Course Tutorial Site

Site Admin

Correlated Queries

without comments

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.

Written by michaelmclaughlin

August 13th, 2018 at 2:43 pm

Posted in