CODEWITHSUNDEEP
sqloracle11g
BSeitkazin
BSeitkazin

Reputation: 3059

Can't update data via select statement

I have a such sql query for updating data:

UPDATE (
    SELECT 
        cal.year_no,
        pi.bin,
        pi.tin,
        epa.fc_fc_id,
        epa.ec_ec_id,
        NVL(epa.COMMIT_OBLIGATION, 0) as pay_amount,
        cal.month_no as months,
        EPA.PAY_AMOUNT_MONTH,
        EPA.PAY_AMOUNT_CALC_DATE
    FROM 
        summary_of_expenditures epa
        INNER JOIN calendar cal ON (epa.cal_cal_id_pay = cal.cal_id) 
        INNER JOIN public_institution pi ON (epa.pi_pi_id = pi.pi_id) 
    WHERE (
        (epa.fc_fc_id is not null) 
        AND (epa.ec_ec_id is not null) 
        AND (cal.month_no IN ('11'))
    ) 
    GROUP BY 
        cal.year_no,
        cal.month_no,
        epa.COMMIT_OBLIGATION,
        pi.bin,
        pi.tin,
        epa.fc_fc_id,
        epa.ec_ec_id,
        EPA.PAY_AMOUNT_MONTH,
        EPA.PAY_AMOUNT_CALC_DATE) 
SET 
    PAY_AMOUNT_MONTH =  pay_amount, 
    PAY_AMOUNT_CALC_DATE = SYSDATE;

When I execute it, I have a follow exception: 

SQL Error: ORA-01732: data manipulation operation not legal on this view.

Please, show me, what I am doing wrong, or suggest me method to update my data.

Upvotes: 0

Views: 54

Answers (1)

druidicwyrm
druidicwyrm

Reputation: 500

You cannot use a GROUP BY in an correlated update statement as this will generate a view.

Upvotes: 1

Related Questions