Correlated update in Oracle

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin? Oracle development languages How to check SQL query construction with the Mimer Validator Understanding SQL string functions The top advice from Oracle experts in 2008 What's the difference between an SQL inner join and equijoin? Using LEFT OUTER JOIN query to get zero row counts in SQL How to return multiple values for THEN clause in an SQL CASE expression Can I concatenate row values in SQL? Should I try to avoid a LEFT OUTER JOIN in SQL? Tips for derived tables in SQL and using FULL OUTER JOINs How to write an SQL query for two foreign keys to the same 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

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.