I did what Denis suggested and it did not work. Here is why:
It updated ALL rows in mytable. I need to update only rows that sutisfy
the conditions c.id=a.id, not ALL rows.
I also need some help here.
Alex
In article <7gv79d$jc_at_dns.apro.fr>,
"Denis ALEXANDRE" <alex_at_apro.fr> wrote:
> 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
> >
> >
> >
>
>
--== Sent via Deja.com http://www.deja.com/ ==--
---Share what you know. Learn what you don't.---
Received on Fri May 14 1999 - 17:14:43 CDT
