Re: Fun with non-trivial update statement

Or SIMPLIFY to:

update mytable a
set a.value =
  (select a.value * c.value
    from mytable c
    where c.id = a.id + 1);

bjin <jinbo_at_hpsgns1.sgp.hp.com> a écrit dans le message : 37316846.26787E92_at_hpsgns1.sgp.hp.com...
> 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 Fri May 07 1999 - 12:14:39 CDT