sam2929
asked on
update issue
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
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
I just re-read the update statement. Since you are updating the same table you are selecting from MERGE becomes more difficult.
ASKER
Error at Command Line : 58 Column : 1
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you provide some sample data and the expected results from that data?
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.