How Update Statement using Derived Table in Oracle?

Question

UPDATE Actual
SET Actual.tranamt=ABS(Actual.tranamt-Reversal.tranamt),
Actual.tranamt_org = ABS(Actual.tranamt),
Actual.trans_flag = 'U'
FROM
(SELECT *
FROM "MATCHPOINT_DEV"."GTT_temp_raw_neft_cbs_iss"
) AS Actual
INNER JOIN
(SELECT *
FROM "MATCHPOINT_DEV"."GTT_temp_raw_neft_cbs_iss" WHERE (reversalrrn IS NOT NULL OR reversalrrn <> '')
) AS Reversal
ON Actual.rrn = Reversal.reversalrrn ;

Getting error

SQL Error: ORA-00933: SQL command not properly ended

00000 - "SQL command not properly ended"

Does Oracle really support that non-standard UPDATE FROM syntax? — jarlh, Nov 24 '16 at 13:25
That's not valid syntax for Oracle. Instead, you should look at doing the update in a MERGE statement. — Boneist, Nov 24 '16 at 13:39

score 1 · Answer 1 · answered Nov 24 '16 at 13:47

One method uses merge. Another method, subqueries:

UPDATE "MATCHPOINT_DEV"."GTT_temp_raw_neft_cbs_iss" Actual                                   
    SET tranamt = 
         (SELECT ABS(Actual.tranamt - x.tranamt),                        
          FROM "MATCHPOINT_DEV"."GTT_temp_raw_neft_cbs_iss" reversal
          WHERE (reversal.reversalrrn IS NOT NULL OR reversal.reversalrrn <> '') and
                Actual.rrn = Reversal.reversalrrn 
         ),                       
       tranamt_org  =  ABS(Actual.tranamt),                                
       trans_flag = 'U'                          
WHERE EXISTS (SELECT 1
              FROM "MATCHPOINT_DEV"."GTT_temp_raw_neft_cbs_iss" reversal
              WHERE (reversal.reversalrrn IS NOT NULL OR reversal.reversalrrn <> '') and
                    Actual.rrn = Reversal.reversalrrn 
             );

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

Oracle does not support that query You can try the following format in this thread Update statement with inner join on Oracle

edited May 23 '17 at 12:13

Community

1
1

answered Nov 24 '16 at 13:41

gusviantoko dali purwanto

11
2

How Update Statement using Derived Table in Oracle?

2 Answers2