Correlated update in Oracle
SQL expert Rudy Limeback explains how a query is meant to be a correlated update.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.