update issue

sam2929
sam2929 used Ask the Experts™
on
Select works but when I do update I get error

UPDATE DIM t1
SET CURR_FLG=
SELECT
CASE WHEN A.RN=1 THEN 'Y' ELSE 'N' END as CURR_FLG
FROM
(
SELECT  
EMPLID,
EMPL_RCD,
EFFDT,
LST_ASGN_START_DT,
EMPL_STATUS,
ROW_NUMBER() OVER (PARTITION BY EMPLID,EMPL_RCD ORDER BY EFFDT DESC) AS RN
FROM DIM
WHERE EFFDT<=SYSDATE)A
WHERE t1.JOB_SID=A.JOB_SID
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What is the error?

When I see an update like this, I think of MERGE:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

There are many examples out there.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I just re-read the update statement.  Since you are updating the same table you are selecting from MERGE becomes more difficult.

Author

Commented:
Error at Command Line : 58 Column : 1
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:    
*Action:
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Try wrapping the select in parenthesis:
UPDATE DIM t1
 SET CURR_FLG=
 (SELECT
 CASE WHEN A.RN=1 THEN 'Y' ELSE 'N' END as CURR_FLG
 FROM
 (
 SELECT  
 EMPLID,
 EMPL_RCD,
 EFFDT,
 LST_ASGN_START_DT,
 EMPL_STATUS,
 ROW_NUMBER() OVER (PARTITION BY EMPLID,EMPL_RCD ORDER BY EFFDT DESC) AS RN
 FROM DIM
 WHERE EFFDT<=SYSDATE)A
 WHERE t1.JOB_SID=A.JOB_SID 
)

Open in new window

awking00Information Technology Specialist

Commented:
Can you provide some sample data and the expected results from that data?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial