Home > Ask the Oracle Experts > SQL Questions & Answers > Correlated update in Oracle
Ask The Oracle Expert: Questions & Answers
EMAIL THIS

Correlated update in Oracle

Rudy Limeback EXPERT RESPONSE FROM: Rudy Limeback

Pose a Question
Other Oracle Categories
Meet all Oracle Experts
Become an Expert for this site


Oracle tips, scripts, and expert advice
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


>
QUESTION POSED ON: 17 April 2008
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."


>
EXPERT RESPONSE

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.


Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


RELATED CONTENT
SQL
IN list or series of OR conditions?
Connecting tables in a database
SQL query for co-authored books
Querying complex derived tables
SQL string functions
Changing a NULL column to NOT NULL
SQL for hourly totals for the last 48 hours
LEFT OUTER JOIN to a MIN/MAX row
Normalizing a crosstab table
Querying metadata and data at the same time

Oracle development languages
IN list or series of OR conditions?
Connecting tables in a database
SQL query for co-authored books
Querying complex derived tables
Oracle 11g: PL/SQL Basics
SQL string functions
Changing a NULL column to NOT NULL
SQL for hourly totals for the last 48 hours
LEFT OUTER JOIN to a MIN/MAX row
Normalizing a crosstab table

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary



Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and answer pairs from more than 250 TechTarget industry experts.
Browse our Expert Advice

HomeNewsTopicsTipsAsk the ExpertsMultimediaWhite PapersProductsBlogs
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Reprints  |  Site Map




All Rights Reserved, Copyright 2003 - 2008, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts