Re: Fun with non-trivial update statement

It seems that you have a unique index
on mytable.id.

So ...

update mytable a
set a.value = (
  select b.value * c.value
  from mytable b, mytable c
  where b.id = c.id - 1
  and a.id = b.id);

Hth
bjin

claggett_at_my-dejanews.com wrote:

> Hello,
>
> I've recently been converting an Update statement from MS SQL to Oracle. The
> MS SQL version of the update utilizes a from clause and looks something like
> this:
>
> UPDATE a
> SET a.value = a.value * b.value
> FROM mytable a, mytable b
> WHERE a.id = b.id - 1
>
> Based on Oracle documentation one of the standard ways to translate this kind
> of update is to use a subquery something like this like this:
>
> UPDATE mytable a
> SET a.value = a.value * b.value -- ERROR: Unknown table: b
> WHERE EXISTS (
> SELECT *
> FROM mytable b
> WHERE a.id = b.id - 1)
>
> The problem I face is that, by putting table b in a subquery, I lose the
> ability to use b's columns in the SET portion of the update statement. Can
> you even do this kind of update in Oracle!? If so, I'd be quite curious as to
> how you did it :)
>
> Thanks for your time,
> Jonathan Claggett
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu May 06 1999 - 05:00:38 CDT