Correlated update in Oracle

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.