Fun with non-trivial update statement

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 Wed Apr 28 1999 - 17:34:16 CDT