0

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.

BSeitkazin
  • 2,889
  • 25
  • 40
  • It looks as though you are trying to update the data in the table returned from the sql SELECT query. I don't know much about Oracle, but that doesn't sound like it would be possible. – druidicwyrm Apr 14 '15 at 04:57
  • @druidicwyrm, I followed that [answer](http://stackoverflow.com/a/7031405/3631743) – BSeitkazin Apr 14 '15 at 04:59
  • 1
    Interesting, I've never seen that before. However, I do believe I see what is different between the two. Your group by will cause the query to generate a view. – druidicwyrm Apr 14 '15 at 05:03
  • I added an answer below so there is a solution for anyone who comes across this in the future. – druidicwyrm Apr 14 '15 at 05:20

1 Answers1

1

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

druidicwyrm
  • 480
  • 2
  • 11