Re: Loading Data...

<snip>

> there are 2 general syntaxes for that:
>
> SQL> create table t1 ( a int primary key, b varchar2(25) );
> Table created.
>
> SQL> create table t2 ( x int , y varchar2(25) );
> Table created.
>
> SQL> insert into t1 values ( 1, 'table 1 data' );
> SQL> insert into t1 values ( 2, 'table 1 data' );
>
> SQL> insert into t2 values ( 1, 'table 2 data' );
> SQL> insert into t2 values ( 2, 'table 2 data' );
> SQL> insert into t2 values ( 3, 'table 2 data' );
> SQL> commit;
> Commit complete.
>
> SQL> select * from t2;
>
> X Y
> ---------- -------------------------
> 1 table 2 data
> 2 table 2 data
> 3 table 2 data
>
> SQL> update ( select * from t1, t2 where t2.x = t1.a )
> 2 set y = b
> 3 /
> 2 rows updated.
>
> SQL> select * from t2;
>
> X Y
> ---------- -------------------------
> 1 table 1 data
> 2 table 1 data
> 3 table 2 data
>
> SQL> rollback;

<snip>

>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>

I hadn't realised you could use 'in-line' views in UPDATE statements.

Why can't you update the t1 table in a similar way (set b = y) though?

  1 update ( select * from t1, t2 where t2.x = t1.a )   2* set b = y

update ( select * from t1, t2 where t2.x = t1.a ) *
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Chris May

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 18 1999 - 09:14:54 CST