Q
Problem solve Get help with specific problems with your technologies, process and projects.

Correlated update in Oracle

SQL expert Rudy Limeback explains how a query is meant to be a correlated update.

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.

This was last published in April 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close