I am trying to bind the data in the top row of a sub select to the column in an update statement. I've attached the PL/SQL code snippet.
I get the following error: ORA-00904: "TMP"."ACCT_ID": invalid identifier
I believe this is due to the fact that the sub query isn't directly linked to the main update statement's table but I don't know how to make this connection using an update statement.
Any help would be greatly appreciated.
UPDATE CM_CO$DLQCS TMP
SET MASTER_SA_ID = ( SELECT SA_ID
FROM ( SELECT SA.SA_ID
FROM CI_SA SA
, CI_SA_TYPE SA_TYPE
WHERE SA.ACCT_ID = TMP.ACCT_ID
AND SA.SA_TYPE_CD = SA_TYPE.SA_TYPE_CD
AND SA_TYPE.SUB_SA_SW = 'N'
AND SA_TYPE.SVC_TYPE_CD = 'E'
AND SA.SA_STATUS_FLG < 40
ORDER BY SA.START_DT DESC
WHERE ROWNUM = 1