Fun with a non-trivial update statement

Hello,

I've got an update statement written in MS SQL that I am trying to translate into Oracle SQL.

In MS SQL the statement looks somthing like:

UPDATE A
  SET A.Value = A.Value * B.Value;
  FROM mytable A, mytable B
  WHERE A.ID = B.ID - 1 The problem that I have run into is that there is no FROM clause in Oracle. According to a bit of Oracle documentation, they suggest that I rewrite MS SQL update statements with FROM clauses as:

UPDATE mytable A
  SET A.Value = A.Value * B.Value
  WHERE A.ID = (
    SELECT B.ID - 1
      FROM mytable B)

Naturally, the above will not work since I am trying to use B.Value outside of the sub-query in which B is defined. So, does anyone know how to write an update statement in Oracle that allows me to use a value joined in from another table that is not being updated?

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 - 14:14:57 CDT