Ask The Oracle Expert: Questions & Answers

Correlated update in Oracle

Correlated update in Oracle

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

We want to move some of the records of a column of one table to another table. We are using Oracle 9i. The query I tried based on your suggestions to one of the questions is as follows:

UPDATE TF_IMP_LC_REQUEST SET DATE_OF_ISSUE = 
(SELECT DATE_OF_LCO_LCA
FROM TF_IMP_LC_STATEMENT,TF_IMP_LC_REQUEST WHERE
TF_IMP_LC_STATEMENT.RUNNING_SERIAL_NUMBER =
TF_IMP_LC_REQUEST.RUNNING_SERIAL_NUMBER) WHERE EXISTS (SELECT
DATE_OF_LCO_LCA FROM TF_IMP_LC_STATEMENT,TF_IMP_LC_REQUEST
WHERE TF_IMP_LC_STATEMENT.RUNNING_SERIAL_NUMBER =
TF_IMP_LC_REQUEST.RUNNING_SERIAL_NUMBER)

This query is not working. I am getting "ORA-01427: Single row sub query returns more than one query."

Actually, the error is "... returns more than one row." But that's a minor quibble. I knew what you meant, and your query confirms it. However, the first thing I had to do, before I could even begin, was to reformat your query.

I honestly don't understand how people can write SQL without indentation. It's ~so~ counter-productive.

Let's have a look at your query, reformatted:

UPDATE TF_IMP_LC_REQUEST 
   SET DATE_OF_ISSUE = 
       ( SELECT DATE_OF_LCO_LCA
           FROM TF_IMP_LC_STATEMENT
              , TF_IMP_LC_REQUEST 
          WHERE TF_IMP_LC_STATEMENT.RUNNING_SERIAL_NUMBER 
              = TF_IMP_LC_REQUEST.RUNNING_SERIAL_NUMBER ) 
 WHERE EXISTS 
       ( SELECT DATE_OF_LCO_LCA 
           FROM TF_IMP_LC_STATEMENT
              , TF_IMP_LC_REQUEST
          WHERE TF_IMP_LC_STATEMENT.RUNNING_SERIAL_NUMBER 
              = TF_IMP_LC_REQUEST.RUNNING_SERIAL_NUMBER )

This is obviously meant to be a correlated update. At least, it seems obvious, because the table being updated, TF_IMP_LC_REQUEST, is one of the tables used inside the subqueries. Notice that the subqueries join the target table TF_IMP_LC_REQUEST to a second table, TF_IMP_LC_STATEMENT. However, the subqueries are uncorrelated, which means that they will return more than one row.

Try the following:

UPDATE TF_IMP_LC_REQUEST    REQ
   SET DATE_OF_ISSUE = 
       ( SELECT DATE_OF_LCO_LCA
           FROM TF_IMP_LC_STATEMENT
          WHERE RUNNING_SERIAL_NUMBER 
              = REQ.RUNNING_SERIAL_NUMBER ) 
 WHERE EXISTS 
       ( SELECT DATE_OF_LCO_LCA
           FROM TF_IMP_LC_STATEMENT
          WHERE RUNNING_SERIAL_NUMBER 
              = REQ.RUNNING_SERIAL_NUMBER )

Notice the alias REQ assigned to the table being updated. This alias is used to correlate the rows selected by the subqueries.

This query will work only if the TF_IMP_LC_STATEMENT table has no more than one row for each TF_IMP_LC_REQUEST row.