Re: complex update

From: <utefan001_at_gmail.com>
Date: 11 Jul 2006 04:35:08 -0700
Message-ID: <1152617708.868208.35750@m79g2000cwm.googlegroups.com>

Mark C. Stock wrote:
> "Martin T." <bilbothebagginsbab5_at_freenet.de> wrote in message
> news:1152600735.068273.198020_at_75g2000cwc.googlegroups.com...
> : utefan001_at_gmail.com wrote:
> : > I have an update that is just not working. When I run it on the latest
> : > oracle 9i release SQL Plus returns this error.
> : >
> : > 01732 "data manipulation operation not legal on this view"
> : >
> : > Note that NONE of the tables used below are views. I know I should
> : > provide more details, but I should also get 3 hours of sleep tonight.
> : >
> : > update (
> : > select jc.llm as foo, sum(act.cost_amt) bar
> : > FROM prod.je_sub js,
> : > ...
> : >
> :
> : You are actually using a view here - an inline view that you are trying
> : to update.
> :
> : > AND act.task_id = ' 3'
> : > group by jc.llm)
> : >
> :
> : As far as I know it is not possible to update a View (your inline view)
> : that contains a group by clause.
> :
> : best,
> : Martin
> :

>

> Martin's exactly right.

>

> However, your set clause makes absolutely no sense -- to set the group by
> column to the sum column? Is that what you're actually doing?
>
> ++ mcs

I see how the setting the group by column to the sum column makes no sense. It won't matter because the group by column is always null when this is run. It fact, I will add that requirement to the where clause.  Anyway, this is not the core problem.

I think the solution will be to use a temporary holding table that allows me to work with the results of the "inline view that contains a group by clause".

thanks Received on Tue Jul 11 2006 - 06:35:08 CDT