InnovaDale
asked on
SQL Update statement using nested sub select
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.
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
);
Try this, should work
UPDATE CM_CO$DLQCS TMP
SET MASTER_SA_ID = (select SA_ID from (
select ACCT_ID , SA_ID
FROM (
SELECT SA.ACCT_ID , SA.SA_ID, row_number() over(partition by SA.ACCT_ID order by SA.START_DT desc) rn
FROM CI_SA SA , CI_SA_TYPE SA_TYPE
WHERE
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 )
where rn = 1 ) A
where A.ACCT_ID = TMP.ACCT_ID;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window