| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Fun with a non-trivial update statement
A copy of this was sent to claggett_at_my-dejanews.com
(if that email address didn't require changing)
On Wed, 28 Apr 1999 19:14:57 GMT, you wrote:
>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.
sure there is. Here are 2 ways to do the above. The first works if the table you are joining to is 'key preserved' (eg: if b.id is a primary key for example). The second works regardless (but will fail if b.id isn't unique since the subquery will return >1 value and hence the update doesn't make any sense at all)....
SQL> create table a ( id int primary key, value int ); Table created.
SQL> create table b ( id int primary key, value int ); Table created.
SQL> insert into a values ( 1, 2 );
1 row created.
SQL> insert into a values ( 2, 2 );
1 row created.
SQL> insert into b values ( 1, 3 );
1 row created.
SQL> commit;
Commit complete.
SQL> select * from a;
ID VALUE
---------- ----------
1 2
2 2
SQL>
SQL> update ( select a.value a_value, b.value b_value
2 from a, b 3 where a.id = b.id ) 4 set a_value = a_value * b_value5 /
1 row updated.
SQL>
SQL> select * from a;
ID VALUE
---------- ----------
1 6
2 2
SQL>
SQL> rollback;
Rollback complete.
SQL>
SQL> select * from a;
ID VALUE
---------- ----------
1 2
2 2
SQL> update a
2 set a.value = ( select a.value * b.value from b
3 where a.id = b.id )
4 where exists ( select a.value * b.value from b
5 where a.id = b.id )
6 /
1 row updated.
SQL> select * from a;
ID VALUE
---------- ----------
1 6
2 2
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |