Updating multiple columns in Oracle

Question

If I try to add in (, CCY_TO_BASE_RATE = EXCHANGERATE.MID_RATE) into the update statement it shows the following errors.

ORA-06550: line 8, column 21:
PL/SQL: ORA-00923: FROM keyword not found where expected

ORA-06550: line 6, column 1:
PL/SQL: SQL Statement ignored

However, without that (, CCY_TO_BASE_RATE = EXCHANGERATE.MID_RATE), the statement is valid. So how would I need to edit my codes so that the statement will be valid?

DECLARE
    v_system_base_ccy NVARCHAR2(10);
BEGIN
    v_system_base_ccy := dbo.Fn_parameter('SYSTEMBASECCY');

    UPDATE tbl_dvcollateral A
    SET    coll_value_base = (SELECT coll_value * Nvl(EXCHANGERATE.mid_rate, 1),
                                     ccy_to_base_rate = EXCHANGERATE.mid_rate
                              FROM   (SELECT mid_rate,
                                             from_ccy
                                      FROM   TABLE(
                                     dbo.Fn_exchangeratetable(:V_AS_OF_DATE,
                                     NULL,
                                     v_system_base_ccy) )
                                     )EXCHANGERATE
                              WHERE  A.ccy = EXCHANGERATE.from_ccy(+)),
           last_updated_by = :V_LOGIN_ID,
           last_updated_datetime = To_timestamp(To_char(systimestamp,
                                                'YYYY-MM-DD HH24:MI:SS'),
                                   'YYYY-MM-DD HH24:MI:SS')
    WHERE  as_of_date = :V_AS_OF_DATE
           AND record_status_id = :V_AUTHORIZED;
END;

Please edit your question and include the complete definition of table TBL_DVCOLLATERAL. Also include an explanation of what DBO.FN_EXCHANGERATETABLE returns. Thanks. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jan 12, 2015 at 3:42

Alex Poole · Accepted Answer · 2015-01-12 07:51:31Z

1

I may be misinterpreting what you're doing, but you seem to have the target column on the wrong side of the update statement:

UPDATE tbl_dvcollateral A
SET    (coll_value_base, ccy_to_base_rate) =
                         (SELECT coll_value * Nvl(EXCHANGERATE.mid_rate, 1),
                                 EXCHANGERATE.mid_rate
                          FROM   (SELECT mid_rate,
...

answered Jan 12, 2015 at 7:51

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

René Nyffenegger · Accepted Answer · 2015-01-12 07:59:37Z

0

In line 8 you have

ccy_to_base_rate = EXCHANGERATE.mid_rate

as a select-expression. This is technically a boolean which Oracle SQL does not tolerate. You need to remove this expression. I cannot tell what you need to replace it with since I have no idea what you tried to formulate.

answered Jan 12, 2015 at 7:59

René Nyffenegger

40.9k36 gold badges180 silver badges321 bronze badges

Collectives™ on Stack Overflow

Updating multiple columns in Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related